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

 

[HongRu]

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

IF you want to sort, better transform the "040512" Text format to Datevalue format.
try this. (suppose "040512" is in A1 cell)

=DATE(RIGHT(A1,2)+2000,LEFT(A1,2),MID(A1,3,2))

and you can use formula "TEXT" to format "mm/dd/yyyy".
try this

=TEXT(B1,"mm/dd/yyyy")

of course, you can combine above two formula to get the result directly.

=TEXT(DATE(RIGHT(A1,2)+2000,LEFT(A1,2),MID(A1,3,2)),"mm/dd/yyyy")

 

[Sahak]

Perfect, I will. Thanks again.

 

[Peter_SSs]

try this. (suppose "040512" is in A1 cell)

=DATE(RIGHT(A1,2)+2000,LEFT(A1,2),MID(A1,3,2))

That would require column A to specifically be formatted as Text prior to any data entry. If the format is General then the leading zero will be dropped and that formula would return incorrect results.

of course, you can combine above two formula to get the result directly.

=TEXT(DATE(RIGHT(A1,2)+2000,LEFT(A1,2),MID(A1,3,2)),"mm/dd/yyyy")

But then any date sorting would not work as the text values would be sorted alphabetically.

@Sahak

I have removed the 'Mark as solution' from post #12 as clearly it is not the post containing the solution to your question. Once you have decided, mark the actual post that contains the solution. If no post contains a solution then don't mark any.

I would try this as a formula approach.
Column A can be formatted as General or Text (mine is General - hence the leading zero was dropped from my A1 entry)
Column B is formatted as the Date format mm/dd/yyyy
Any sorting based on column B should sort in correct date order.

Sahak.xlsm
	A	B
1	12522	01/25/2022
2	110321	11/03/2021
Sheet3

Cell Formulas
Range	Formula
B1:B2	B1	=DATE(20&RIGHT(A1,2),LEFT(TEXT(A1,"000000"),2),MID(TEXT(A1,"000000"),3,2))

 

[Sahak]

Sorry about marking in the wrong place, I will note that, so I'm putting it now on post #13, I think it will be correct

 

[Peter_SSs]

Sorry about marking in the wrong place,

No problem - just try to remember for the future.

 

[Sahak]

No problem - just try to remember for the future.

Promise will remember!