Excel Macro to Convert yymmdd Date Format to a Serial Number

ID: Q44736

The information in this article applies to:

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: <<Name of macro>>
   A2: =DATE(LEFT(ACTIVE.CELL(),2),MID(ACTIVE.CELL(),3,2),

        RIGHT(ACTIVE.CELL(),2))

   A3: =FORMULA(A2)
   A4: =FORMAT.NUMBER("mm-dd-yy")
   A5: =SELECT("R[1]C")
   A6: =IF(ACTIVE.CELL()<>"",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 "two-digit year" 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        :

Last Reviewed: July 23, 1999