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

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:

Hi,

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

Just kidding... 🙂

cheers

Janis

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

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