Internal Excel date format and date calculations in SAP

[franzstrohmeier]

It is commonly known, that Excel stores dates internally as numbers. So, when uploading XML-Files based on Excel or when using Excel tables without date formatting, you might get something like this:

Expected	Provided
2014/01/01	41640
2014/05/15	41774
2013/06/25	41450
1998/01/01	35796

So, with a little bit of code like this, we could reconstruct in SAP the date requested by adding the number of days to the base format used by Excel, which is January, 1st, 1900:

REPORT  z_excel_date_to_sap.

DATA:

   gv_mydate TYPE sy-datum.

PARAMETERS:

   p_days TYPE i OBLIGATORY.

START-OF-SELECTION.

   gv_mydate = '19000101'.     "Base date used by Excel

   ADD p_days TO gv_mydate.

   WRITE: / 'Excel-Basedate 1900/01/01 +', p_days,

            'days gives', gv_mydate.

So let's try the first example:

What's that? We are two days wrong!

A little investigation, eg by entering 1900/01/01 in Excel shows, that counting of days in Excel starts with 1. Therefore, day 1 is the first of January 1900. So when adding days we have to subtract one.

Ok, so change the code and let's hope that something magic will happen:

  gv_mydate = gv_mydate + p_days - 1.  "Because of 1900/01/01 is day 1.

Quite close, but still very wrong. The reason for this is explained by Chip Pearson in http://www.cpearson.com/excel/datetime.htm

The Excel day counting assumes that there was a February, 29th, 1900 (which was not!) in order to stay combatible with Lotus-1-2-3 formulas (where it was a bug) and worksheets.

So when using the internal date representation of Excel for date calculations, we have to subtract 2 in order to get a correct result:

  gv_mydate = gv_mydate + p_days - 2.  "Because of 1900/01/01 is day 1 and Excel

                                                                              "counts a non-existing 1900/02/29

Now we get the correct result for our examples, here shown  for the first one: