Skip to Content
Author's profile photo Franz Strohmeier

Internal Excel date format and date calculations in SAP

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.


   gv_mydate TYPE sydatum.


   p_days TYPE i OBLIGATORY.


   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

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:


Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Jānis B
      Jānis B


      "Excel-Basedate 1900/01/01 +          1  days gives 31.12.1899".

      Just kidding... 🙂



      Author's profile photo Franz Strohmeier
      Franz Strohmeier
      Blog Post Author


      you are totally right. This code would never pass a quality check. 😉

      So let's try to correct the code before it causes harm:

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

        IF p_days EQ 60.

      *   No, I will not create a message for this demo!

          MESSAGE  'You are trying to fool me! That day never existed!'(x01) TYPE 'X'.

        ELSEIF p_days LT 60.

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


          gv_mydate = gv_mydate + p_days - 2.           "Excel counts a non-existing 1900/02/29


      Kind regards,


      Author's profile photo Tomas Buryanek
      Tomas Buryanek

      This reminds me My First BillG Review - Joel on Software , where you can read little "MS Excel inside" info about this bug 🙂