XL: Converting Dates from yy/mm/dd to mm/dd/yy FormatLast reviewed: December 18, 1996Article ID: Q113616 |
The information in this article applies to:
SUMMARYFiles imported from mainframes and other systems may contain dates in the format yy/mm/dd. These date formats are not recognized by Microsoft Excel and are imported as text strings.
WORKAROUNDS
Method 1: Convert text to columnsIn Microsoft Excel versions 5.x, 7.0, and 97, the following steps convert the data to correctly formatted dates as serialized date numbers.
Method 2: Use a formulaIn any version of Microsoft Excel, to convert the date text to a serial number, enter the following formula in the worksheet:
B2: =DATEVALUE(MID(A2,4,2)&"/"&RIGHT(A2,2)&"/"&LEFT(A2,2))The above formula assumes that the date to be converted is in cell A2.
Method 3: Use a macroMicrosoft Excel 4.0 Macro Example: Microsoft provides macro examples for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The following macro converts the date, to a serial number. If the separator between the yy mm and dd values is not a forward slash (/), then modify the SEARCH statements within the macro, replacing all occurrences of the forward slash with the appropriate character. A1: Convert_Date A2: =SEARCH("/",ACTIVE.CELL()) A3: year=MID(ACTIVE.CELL(),1,A2-1) A4: =SEARCH("/",ACTIVE.CELL(),A2+1) A5: month=MID(ACTIVE.CELL(),A2+1,A4-A2-1) A6: day=MID(ACTIVE.CELL(),A4+1,2) A7: =FORMULA(DATEVALUE(month&"/"&day&"/"&year)) A8: =SELECT("r[1]c") A9: =IF(ACTIVE.CELL()<>"",GOTO(A2),RETURN()) The above macro assumes the following:
|
KBCategory: kbusage kbmacro kbhowto
© 1998 Microsoft Corporation. All rights reserved. Terms of Use. |