Microsoft KB Archive/75770

{|
 * width="100%"|

Converting Mainframe Date Formats into Excel Date Formats

 * }

-

The information in this article applies to:


 * Microsoft Excel for Windows, versions 2.1, 3.0, 4.0, 4.0a, 5.0
 * Microsoft Excel for OS/2, version 2.2, 3.0

-

SUMMARY
Files created by mainframe programs will frequently contain dates entered as numbers in the format

  yymmdd

where &quot;yy&quot; is the year, &quot;mm&quot; is the month number, and &quot;dd&quot; is the day number in the date. Numbers in this format can be converted to Microsoft Excel dates using the following formula

  =Date(Left(number,2),Mid(number,3,2),Right(number,2))

where &quot;number&quot; is either the number or a cell reference to the number to be converted.

If the date is in the format mmddyy or mddyy, use the following formula:

  =DATE(MOD(A1,100),TRUNC(A1/10000),MOD(TRUNC(A1/100),100)) Additional query words: 2.0 2.00 2.01 2.1 2.2 2.20 2.21 3.0 4.0 4.00a 5.0

Keywords :

Version : 2.x 3.x 4.00 4.00a 5.00 | 2.20 2

Platform : OS/2 WINDOWS

Issue type :

Technology :