Microsoft KB Archive/44736

{|
 * width="100%"|

Excel Macro to Convert yymmdd Date Format to a Serial Number

 * }

-

The information in this article applies to:


 * Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 4.0a, 5.0

-

SUMMARY
Dated records from a mainframe may import as yymmdd (for example, January 1, 1991 is represented as 910101) in Excel. The format is not recognized by Excel and must be converted to a serial number.

MORE INFORMATION
The following short macro converts a mainframe date, with no separators, to Excel format:

  A1: <> A2: =DATE(LEFT(ACTIVE.CELL,2),MID(ACTIVE.CELL,3,2),

RIGHT(ACTIVE.CELL,2))

A3: =FORMULA(A2) A4: =FORMAT.NUMBER(&quot;mm-dd-yy&quot;) A5: =SELECT(&quot;R[1]C&quot;) A6: =IF(ACTIVE.CELL<>&quot;&quot;,GOTO(A2),RETURN) The above macro assumes the following:


 * 1) The dates to be converted are arranged in columns.
 * 2) The active cell is the first date to be converted in the column.
 * 3) There is an empty cell at the end of the column of dates.

NOTE: This macro applies the &quot;two-digit year&quot; rule to dates. For more information, please see the following article in the Microsoft Knowledge Base:

"Q164406 XL: How Microsoft Excel Works with Two-Digit Year Numbers" Additional query words: 2.10 2.1 3.0 4.0

Keywords :

Version :

Platform :

Issue type :

Technology : kbvcSearch