As the first step in the decommissioning of sasCommunity.org the site has been converted to read-only mode.


Here are some tips for How to share your SAS knowledge with your professional network.


Tips:Conversion from Excel Date to SAS Date

From sasCommunity
Jump to: navigation, search


As part of the decommissioning effort for sasCommunity.org this article/tip has been migrated to communities.sas.com.

The new home for this article/tip is Conversion from Excel Date to SAS Date (https://communities.sas.com/t5/SAS-Tips-from-the-Community/SAS-Tip-Conversion-from-Excel-Date-to-SAS-Date/m-p/475824#M177)



Many of us have to convert Excel files into SAS datasets. Converting date/time value from Excel to SAS can be a hassle as both Excel and SAS having different reference dates (i.e. Day 0 in SAS is 1 January 1960 and Day 0 in Excel is 1 January 1900), below formulas offer great help in terms of converting date/time values from Excel to SAS.

  • SAS_date = Excel_date - 21916;
  • SAS_time = Excel_time * 86400;
  • SAS_date_time = (Excel_date_time - 21916) * 86400;

NOTE: Excel has two different datetime "systems" and the default differs depending on the platform. The above formulas assume Excel is using the 1900 date system. If Excel is using the 1904 Date System, use 20454 in the above formulas instead of 21916.

For additional information on the Excel Date Systems, see MS KB article titled XL: The 1900 Date System vs. the 1904 Date System. The 1900 Date system has other well-known quarks, too. For the background of this dual system in the context of a developer's rather funny/scary encounter with Bill Gates, read this blog entry by Joel Spolsky.

Submitted by Koo Ping Shung. Contact me at my Discussion Page.

....read more