How do I convert from SAS integer dates to Excel format? (YYYYMMDD to DD/MM/YYYY)
If you have data from WRDS, SDC Platinum or other sources, you may see dates written as an integer YYYYMMDD, for example, 20200615 for 15 June 2020. Dates of this type are helpful in some situations since you can easily sort them, however, you cannot use Excel's date functions with them. You also need native Excel dates to use a Datastream Request Table.
We can use some Excel functions to convert these integer dates to Excel's native date format (number of days since 1 January 1900) which can then be formatted to display however you like. The format changes depending on the locale of your computer: in the UK, this is often DD/MM/YYYY.
We can add columns for each of the year, month and day values, then combining again at the end. We will also keep the original date format, unchanged. We have to:
- interpret the integer date as a text value,
=TEXT(A2, "0") - then split it up,
=LEFT(text, numchars)or=MID(text, start, numchars) - then convert each part back to an integer,
=NUMBERVALUE(text) - then join them back together
=DATE(year, month, day)
If the date is in cell A2:
- SAS date A2:
20200615 - Year B2:
=NUMBERVALUE(LEFT(TEXT($A2,"0"),4)) - Month C2:
=NUMBERVALUE(MID(TEXT(A2,"0"),5,2)) - Day D2:
=NUMBERVALUE(MID(TEXT(A2,"0"),7,2)) - Excel date E2:
=DATE(B2,C2,D2)
You can also write a single formula to do all these steps in one column.
=DATE(NUMBERVALUE(LEFT(TEXT(A2,"0"),4)), NUMBERVALUE(MID(TEXT(A2,"0"),5,2)), NUMBERVALUE(MID(TEXT(A2,"0"),7,2)))
If you wish to use the new column in another sheet, you can copy then paste-as-values, or save as a CSV file. This process will remove the formulas but keep the values.