Exporting Dates to XLS

Article number: KB000023

General Information

Related blocks:

Problem

If the report contains earlier dates than 01.03.1900, the following feature arises when exporting the report to XLS: date in XLS is shifted one day forward with respect to the date specified in the report. For example:

Report date	Date in MS Excel after exporting the report	Comment
26.02.1900	27.02.1900	One day discrepancy
27.02.1900	28.02.1900	One day discrepancy
28.02.1900	29.02.1900	One day discrepancy
01.03.1900	01.03.1900
02.03.1900	02.03.1900

This problem arises due to the following feature: MS Excel uses Julian calendar, so 1900 is considered to be a leap year and February has 29 days, while Foresight Analytics Platform uses Gregorian calendar data, so 1900 is not a leap year and February has 28 days.

Solution

To avoid this problem, it is recommended to format report dates earlier than 01.03.1900 as text and not as a date. Thus, one-day discrepancy will be avoided after exporting the report to XLS.