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 sydatum.

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:

/wp-content/uploads/2014/05/blog01_456580.jpg

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.

/wp-content/uploads/2014/05/blog02_456590.jpg

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:

/wp-content/uploads/2014/05/blog03_456591.jpg

To report this post you need to login first.

3 Comments

You must be Logged on to comment or reply to a post.

  1. Jānis B

    Hi,

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

    Just kidding… πŸ™‚

    cheers

    Janis

    (0) 
    1. Franz Strohmeier Post author

      Hi,

      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

        ELSE.

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

        ENDIF.

      Kind regards,

      Franz

      (0) 

Leave a Reply