Help: How do I convert a text date into a real date format

[japorms]

Hi Guys,

Does anyone know how to convert a date that's inputted in a text
format?

Example: The date is entered as 62606, this means as 6/26/06. I just
can't change the format into date because it will be different.

I'm thinking if there is some sort of formula to add a hyphen and turn
it to 6-26-06. Like extracting, the last two digit, then the two
middle, then the first

Hope someone can help me. Thanks in advance.

 

[Guest]

Hi

What if the 'date' is 10606? Is that 1/06/06 or is it 10/6/06?

Andy.

 

[Guest]

Maybe this............

=IF(RIGHT(A1,2)*1<=6,DATE(RIGHT(A1,2)*1+2000,LEFT(A1,LEN(A1)-4)*1,MID(A1,LEN(A1)-3,2)*1),DATE(RIGHT(A1,2)*1+1900,LEFT(A1,LEN(A1)-4)*1,MID(A1,LEN(A1)-3,2)*1))

All on one line, watch out for wordwrap......

Vaya con Dios,
Chuck, CABGx3

 

[micheldevon]

Well do you need the date to be read as a date by Excel? or only by the
user?

If you don't need Excel to read the number as a date, then there is a
very simple way to solve your problem.

Right click in the cell where the date is to go.
Choose Format Cells from the dropdown.
Click the Number Tab
Category: Custom

In the Type: Box where it says "General" type
00-00-00
or
00/00/00
depending on whether you'd prefer hyphens or slashes.

And then Click OK.

You can now type in 062606 or 62606 and it will be changed to 06/26/06
or 06-26-06.
Excel won't read it as date, but it will look like a date.

It depends on what else you need the cell to do (i.e. do any other
cells refer to this cell in a formula?) If that is the case then this
won't work for you, but if you only need it to "look" like a date for
printed forms or whatever, this should work.

 

[SteveG]

You could use this if your day value is always 2 digits (1st = 01,
2nd=02 etc..) So October 10 is 101006 and January 1 is 10106

=TEXT(A1,"00-00-00")*1

Format cells as mm/dd/yy.



HTH

Steve