Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
TEXT DATE Format Conversion
When I have a text file that I open in Excel, the field that brings in the
month is in text as "JAN-07" meaning January 2007. All of my period names are in this format from the oracle output. Just so I can give you a few more examples: JAN-06 is supposed to be January 2006, FEB-05 is supposed to be February 2005, and DEC-99 is supposed to be December 1999. I want to change the format of this field to something that will keep this relationship because Excel automatically assumes that I want: JAN-06 to be January 06, 2007, FEB-05 to February 05, 2007, etc...... It uses the current year to complete the date. I am currently using "[$-409]mmm-yy;@" as the format to change it to: JAN-06 to be Jan-07, FEB-05 to be Feb-07, etc.... Will someone please help me? -- Ashish |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
TEXT DATE Format Conversion
If you have JAN-07 in A1 as text, then
=DATEVALUE("1/" & LEFT(A1,3) & "/20" & RIGHT( A1,2)) will give you a real date, you should format this cell as a date. -- Gary's Student gsnu200705 "Ashish_Vaidya" wrote: When I have a text file that I open in Excel, the field that brings in the month is in text as "JAN-07" meaning January 2007. All of my period names are in this format from the oracle output. Just so I can give you a few more examples: JAN-06 is supposed to be January 2006, FEB-05 is supposed to be February 2005, and DEC-99 is supposed to be December 1999. I want to change the format of this field to something that will keep this relationship because Excel automatically assumes that I want: JAN-06 to be January 06, 2007, FEB-05 to February 05, 2007, etc...... It uses the current year to complete the date. I am currently using "[$-409]mmm-yy;@" as the format to change it to: JAN-06 to be Jan-07, FEB-05 to be Feb-07, etc.... Will someone please help me? -- Ashish |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
permanent conversion of 1904 date format to 1900 date format | Excel Worksheet Functions | |||
Text Date Conversion | Excel Discussion (Misc queries) | |||
Conversion of date into different format | Excel Discussion (Misc queries) | |||
Date format conversion | Excel Discussion (Misc queries) | |||
Date Format - Conversion | Excel Discussion (Misc queries) |