Date Formatting (don't put slashes, but get with slashes)

[Sahak]

I all,
I would like to have the cell formatted MM/DD/YYYY.
Is it possible to format or to use VBA, if I in the cell will enter for example 040512 (no slashes), after finishing the entry, it will show 04/05/2012, if I enter 100421 (no slashes), after finishing the entry, it will show 10/04/2021.
Thank you in advance

 

[Legacy 143009]

strTest = Range("A1").Value
msgbox datevalue(left(strtest,2) & "/" & mid(strtest,3,2) & "/" & right(strtest,2))

 

[Sahak]

strTest = Range("A1").Value
msgbox datevalue(left(strtest,2) & "/" & mid(strtest,3,2) & "/" & right(strtest,2))

Thank you for your reply,
Sorry for the misunderstanding, I don't want to see it on Message Box, I need to have formatted it in the cell, I'm entering numbers in the cell without slashes but in the same cell it getting formatted with slashes.

 

[Legacy 143009]

Ok then,
This will work for you:

Private Sub Worksheet_Change(ByVal Target As Range)
  With Target
    Application.EnableEvents = False
    .Value = Format(CDate(Mid(.Value, 3, 2) & "/" & Left(.Value, 2) & "/" & Right(.Value, 2)), "MM/dd/yyyy")
    Application.EnableEvents = True
  End With
End Sub

 

[HongRu]

try to set the cell custom number format => 00"/"00"/"00
or => 00!/00!/00

refer to

Create and apply a custom number format - Microsoft Support

If a built-in number format does not meet your needs, you can create a new custom number format that is based on an existing number format and add it to the list of custom number formats.

support.microsoft.com

 

[Legacy 143009]

try to set the cell custom number format => 00"/"00"/"00
or => 00!/00!/00

Actually, this is a good suggestion. This will fit better to OP's needs:
00"/"00"/20"00;@

 

[HongRu]

Actually, this is a good suggestion. This will fit better to OP's needs:
00"/"00"/20"00;@

Yes, I agree.

 

[Sahak]

Yes,

00"/"00"/20"00;@ works Thank you all very much

 

[Peter_SSs]

works

Hmm, does that mean that you are not using those dates for any calculations, lookups, comparisons etc?

For example, in the sheet below I have entered 020104 in cell A1 and I have entered the date 02/01/2004 in B1. the formulas in c1, D1 show that they are not the same and in fact as dates would be 17,884 days apart. In fact, if you hover over A1 in my mini sheet the pop-up shows 20104 (which is what I entered but dropped the leading 0) and if you hover over B1 it shows 37988. (You may get different values to what I have just quoted as my date system is d/m/y order, but the concept is just the same)

If it is just a visual thing and you don't care about the underlying actual date, then no problem but if you are going to use that date for something else then there will be problems.

Sahak.xlsm
	A	B	C	D
1	02/01/2004	02/01/2004	FALSE	17884
Sheet1

Cell Formulas
Range	Formula
C1	C1	=A1=B1
D1	D1	=B1-A1

 

[Sahak]

Thank you Peter very much, good catch. it is good to know. It gives a problem with sorting too