Convert MMDDYY to MM/DD/YY?

[bksmu]

I want to be able to type 030511 (or 03052011 if necessary) in a cell and have the cell next to it display 03/05/2011. Is this possible?

[bksmu]

I figured it out. =IF(A2=0," ",LEFT(A2,2)&"/"&MID(A2,3,2)&"/20"&MID(A2,5,2))

[broro183]

hi,

Welcome to the Forum

edit: nice one - I like it when I get beaten to the answer by the poster
end edit

Yes, this is possible. Here are three options:
This will give a text string:
=IF(LEN(A1)=5,LEFT(A1,1)&"/"&MID(A1,2,2)&"/20"&RIGHT(A1,2),IF(LEN(A1)=6,LEFT(A1,2)&"/"&MID(A1,4,2)&"/20"&RIGHT(A1,2),"please check your entry"))
This will return a date value that you can format as desired:
=DATEVALUE(IF(LEN(A1)=5,LEFT(A1,1)&"/"&MID(A1,2,2)&"/20"&RIGHT(A1,2),IF(LEN(A1)=6,LEFT(A1,2)&"/"&MID(A1,4,2)&"/20"&RIGHT(A1,2),"please check your entry")))

or
=IF(LEN(A1)=5,DATE(2000+RIGHT(A1,2),MID(A1,2,2),LEFT(A1,1)),IF(LEN(A1)=6,DATE(2000+RIGHT(A1,2),MID(A1,3,2),LEFT(A1,1)),"please check your entry"))

I'm sure these formulae could be written more eloquently, but I think they will do what you want.

hth
Rob

[Ron Coderre]

First, format Col_A as TEXT.
Then, try this:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Does that help?

[bksmu]

Yes, thanks a lot guys, but now I have another modification I need. If the last 2 numbers are greater than 70, I want it to insert 19 instead of 20. And if the last 2 numbers are less than 70, to insert 20. E.g. 081578 would be 08/15/1978 and 081533 would be 08/15/2033. Or if there is an easier way of doing it, that would be great too. Thanks again by the way.

[Gary Brown]

Try the following as an amendment to your original formula:-

=IF(A2=0," ",LEFT(A2,2)&"/"&MID(A2,3,2)&IF(VALUE(RIGHT(A2,2))<70,"/20","/19")&MID(A2,5,2))

[Ron Coderre]

As long as the inputs only use 2-digit years, would you consider letting Excel decide which century the date belongs in?

Try this:
A1: some date text...
Examples:
081578
012731
091229

Please Login or Register  to view this content.

In the above examples, the formula would return these values:
08/15/1978
01/27/1931
09/12/2029

Is that something you can work with?