Exporting Dates to XLS

Article number: KB000023

General Information

Related blocks:

Problem

If a report contains earlier dates than 03/01/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
02/26/1900	02/27/1900	One day discrepancy
02/27/1900	02/28/1900	One day discrepancy
02/28/1900	02/29/1900	One day discrepancy
03/01/1900	03/01/1900
03/02/1900	03/02/1900

This feature arises due to the fact that 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, it is recommended to format report dates earlier than 03/01/1900 as text and not as a date. Thus, one-day discrepancy will be avoided after exporting the report to XLS.