Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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... |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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... |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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... |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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... |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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... |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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... |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
number to date format | Excel Discussion (Misc queries) | |||
Number format to date | Excel Discussion (Misc queries) | |||
How do you format a number into a date? | Excel Discussion (Misc queries) | |||
* next to date format in Formatcells Number tab | Excel Discussion (Misc queries) | |||
Excel: I enter date and format for date, but shows as number | Excel Discussion (Misc queries) |