Format for Date if you get number

D

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...
 
L

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))
 
D

Don

thanks , the date formula worked

Luke M said:
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!*
Click to expand...
 
S

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))
 
G

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
 
Top