Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
change date format
I have a database that has a date format of yyymmdd. I need this to be
mmddyyyy. How can I change this? -- linda |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
change date format
Strange to have a 3-digit year.
If A1 contains: 0081225 In cell B1 enter: =DATE(2000+LEFT(A1,3),MID(A1,4,2),RIGHT(A1,2)) and format Custom as mmddyyyy to display 12252008 -- Gary''s Student - gsnu200772 "linda" wrote: I have a database that has a date format of yyymmdd. I need this to be mmddyyyy. How can I change this? -- linda |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
change date format
Assuming the 3-digit database year is correct (and not a mistyping of yyyy),
your formula will only work if the year value is in this millennium (for example, it will fail for 997 as the first 3-digits). Here is a modification to your formula that will work for the any between 1/1/1900 and 12/32/2099... =DATE((1+(LEFT(A1)="0"))&LEFT(A1,3),MID(A1,4,2),RI GHT(A1,2)) Also, here is an slightly shorter formula which also works within the same range of years.... =--TEXT((1+(LEFT(A1)="0"))&A1,"0000-00-00") Again, both of the above formulas assume the OP's database date really only has a 3-digit year. If it turns out that the database date pattern was mistyped and should have been really been yyyymmdd, then my proposed formula would be... =--TEXT(A1,"0000-00-00") In all case above, your suggestion to use a Custom format to display the converted date as mmddyyyy still holds. Rick "Gary''s Student" wrote in message ... Strange to have a 3-digit year. If A1 contains: 0081225 In cell B1 enter: =DATE(2000+LEFT(A1,3),MID(A1,4,2),RIGHT(A1,2)) and format Custom as mmddyyyy to display 12252008 -- Gary''s Student - gsnu200772 "linda" wrote: I have a database that has a date format of yyymmdd. I need this to be mmddyyyy. How can I change this? -- linda |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change satellite julian date format YYYYDDDHHMMSS to excel date ti | Excel Discussion (Misc queries) | |||
Use date modified to change format & create filter to track change | Excel Worksheet Functions | |||
Can I change a date with no format (20051111) to date format? | New Users to Excel | |||
Change general format to US date format | Excel Discussion (Misc queries) | |||
how do I format cells to change date and time to just date | Excel Discussion (Misc queries) |