Format for Date if you get number

[Don]

I get a file that has a date but it gives it to me as a number like 1/15/2009
would be 11509 in the cell. Is there a way to take that value and format it
to know it is a date either by having another cell help format it? Note that
if the date is like 1/08/09 then the number would be 10809...

 

[Luke M]

If you just want it to display correctly, go to Format cell, custom format
##"/"##"/"##
If you use the value in formulas and want it converted to an actual date
=DATE(RIGHT(A2,2)+100,LEFT(A2,LEN(A2)-4),MID(A2,LEN(A2)-3,2))

 

[FSt1]

hi
the number you see is the way excel keeps dates. the number is the serial
date number. a date formated as general will show this number. to correct,
just format the serial number as date.
also.... see these sites for more info on date keeping systems that excel
uses.
there are 2.
http://www.cpearson.com/excel/datetime.htm
http://support.microsoft.com/kb/214330

regards
FSt1

 

[Don]

thanks , the date formula worked

If you just want it to display correctly, go to Format cell, custom format
##"/"##"/"##
If you use the value in formulas and want it converted to an actual date
=DATE(RIGHT(A2,2)+100,LEFT(A2,LEN(A2)-4),MID(A2,LEN(A2)-3,2))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

 

[Sheeloo]

Assuming date is in A1
Try
=IF(LEN(A1)=5,DATE(2000+RIGHT(A1,2),LEFT(A1,1),MID(A1,2,2)),DATE(2000+RIGHT(A1,2),LEFT(A1,2),MID(A1,3,2)))

If all dates have only five digits then you can use
DATE(2000+RIGHT(A1,2),LEFT(A1,1),MID(A1,2,2))

 

[T. Valko]

Try this:

=--TEXT(A1,"00\/00\/00")

Format as Date

 

[Gary''s Student]

Your number may be either 5 or 6 digits long, so we will use a "pair" of
dates in the column B formulas:

=IF(LEN(A1)=6,DATE(2000+RIGHT(A1,2),LEFT(A1,2),MID(A1,3,2)),DATE(2000+RIGHT(A1,2),LEFT(A1,1),MID(A1,2,2)))

so if A1 and A2 contain:
122509
82509

then B1 and B2 will display:

12/25/2009
8/25/2009