Date parsing differences between Excel and SpreadJS

When using a custom Short Date format such as 05-April-17, date inputs like Sep-07 may be interpreted differently in Excel and SpreadJS. This difference in parsing behavior can lead to inconsistent calculation results between the two platforms. Understanding how each application interprets ambiguous date values is essential to ensuring consistent behavior.

When the Short Date format is changed to 05-April-17 and the value Sep-07 is entered into a cell, Excel interprets this value as September 1, 2007. In this case, 07 is recognized as the year (2007), and the day defaults to 1. As a result, any calculation based on this date uses September 1, 2007 as the starting point.

SpreadJS, however, parses the same input differently. It interprets Sep-07 as September 7, 2026. In this scenario, 07 is treated as the day of the month, and the year defaults to 2026. Because the underlying parsed date is different, subsequent calculations produce different results compared to Excel.

With the regular Short Date format such as 4/5/2017, both Excel and SpreadJS interpret the value consistently, and the results match. The discrepancy occurs specifically when the custom date format changes how ambiguous text values are recognized and parsed.

The root cause of this issue is the difference in default parsing logic. After modifying the system date settings, Excel recognizes Sep-07 using a month–year pattern and defaults the day to 1. SpreadJS, on the other hand, follows its predefined parsing rules, where the numeric portion is interpreted as the day and a default year is assigned.

To align SpreadJS behavior with Excel, you can adjust the culture’s preselected date format so that MMM-yy is prioritized during parsing. This ensures that Sep-07 is interpreted as September 1, 2007, matching Excel’s behavior and maintaining consistent calculation results.

The following workaround can be applied:

var cultureInfo = GC.Spread.Common.CultureManager.getCultureInfo("en-us");

cultureInfo.DateTimeFormat.preselectedFormatters.unshift('MMM-yy');

var spread = new GC.Spread.Sheets.Workbook('ss');

This adjustment ensures consistent date recognition and calculation results between Excel and SpreadJS.