Format for Date if you get number
 

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

Format for Date if you get number
 

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!*


"Don" wrote:

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

Format for Date if you get number
 

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" wrote:

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

Format for Date if you get number
 

thanks , the date formula worked

"Luke M" wrote:

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!*


"Don" wrote:

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

Format for Date if you get number
 

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

"Don" wrote:

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

Format for Date if you get number
 

Try this:

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

Format as Date

--
Biff
Microsoft Excel MVP


"Don" wrote in message
...
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...

Format for Date if you get number
 

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

--
Gary''s Student - gsnu200828


"Don" wrote:

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