An early Excel date changes after an XLSX workbook is saved and reopened. The source may contain serial 60, the fictional February 29, 1900. HotXLS retains that date-formatted worksheet serial and its fractional times as numbers, because Delphi cannot express the nonexistent day as a calendar date
The 1900 date system assigns serial 60 to February 29, even though 1900 was not a leap year in the Gregorian calendar. The problem extends through the fractional part of that day: 60.5 carries a time of noon on the fictional date. HotXLS keeps date-formatted worksheet serials in the interval from 60 inclusive to 61 exclusive numeric. Retaining the original number gives the workbook a value to write back without forcing an impossible day into a normal calendar type
Why does the extra day exist?
Excel preserves this convention for compatibility with earlier spreadsheet software; Microsoft's explanation of Excel's 1900 leap-year behavior describes the historical decision. The boundary is worth writing down: 59 means February 28, 60 denotes the fictional February 29, and 61 means March 1 in the 1900 system. Fractions represent the time of day. Thus 59.5 is noon on February 28, whereas 60.5 has the same noon fraction on the fictional day. Most contemporary business workbooks never approach that boundary, so a modern invoice can survive a routine save-and-reopen check without exercising this case. Historical imports and conversion tools need a deliberately chosen set of boundary values
The number format is not the number
A cell's numeric value does not announce whether it is a date. Its style refers to a number format that tells the spreadsheet application how to display the number, and the workbook's date system supplies the epoch. The number 60 might be a date-formatted serial or simply a quantity of parts. For the 1900 system, the special case in this article is a date-formatted value in [60, 61); HotXLS retains the number, including its fraction. Carry the raw serial and formatting context alongside your calendar interpretation. A formatted string alone is insufficient evidence for a round trip, because two different stored numbers can be presented in a way that hides which value the source workbook actually contained
Inspect the value before converting it
The first Delphi routine opens an existing workbook and reports the date system, the cell value and its number format. It leaves the decision about whether that format denotes a date to the caller: the numeric value's proximity to 60 does not make an ordinary quantity a date
uses
SysUtils, Variants, lxHandleX;
procedure InspectCell(const FileName: string);
var
Book: TXLSXWorkbook;
Cell: TXLSXCell;
begin
Book := TXLSXWorkbook.Create;
try
if Book.Open(FileName) <> 1 then
raise Exception.Create('Cannot open workbook');
if Book.Sheets.Count = 0 then
raise Exception.Create('Workbook has no sheets');
Cell := Book.Sheets[1].Cells[1, 1];
Writeln('1904 date system: ', BoolToStr(Book.Date1904, True));
Writeln('Variant type: ', VarType(Cell.Value));
Writeln('Value: ', VarToStr(Cell.Value));
Writeln('Number format: ', Cell.NumberFormat);
finally
Book.Free;
end;
end;The example reads row 1, column 1 of the first worksheet. Adapt those coordinates to the cell being investigated and handle the case where the input workbook has no worksheet. The important result is the value available to the application: a date-formatted fictional day can remain numeric and travel through the save path without being converted into a nonexistent calendar date
Why can calendar conversion lose serial 60?
Delphi's TDateTime is convenient for real dates, but it has no distinct calendar state for February 29, 1900. If an import pipeline maps both the preceding real day and the fictional day to the same calendar date, an exporter cannot infer which Excel serial was originally present. The fraction exposes the problem clearly. Converting 60.5 to February 28 at noon and later writing that date can yield 59.5: noon survived, while the worksheet day changed. Dropping the fraction instead would turn noon into midnight. A model that retains the raw serial and its number format can preserve the source value even when its calendar conversion is unavailable
For ordinary dates, a parsed calendar value is often the useful form for business rules. Alongside it, an application can retain the raw number and the workbook's date system; for the fictional interval, that raw value remains the interchange value. If users need to edit it, the application should explain why it cannot be represented as an ordinary calendar date. The predicate below checks the date system and the numeric interval before conversion. The caller supplies whether the cell's format is interpreted as a date, and existing date-typed values are left alone. We prefer making that decision at the conversion boundary, where both the number and its format are still available
function IsFictionalExcelDay(const Value: Variant;
Uses1904System, IsDateFormatted: Boolean): Boolean;
var
Serial: Double;
begin
Result := False;
if Uses1904System or not IsDateFormatted then Exit;
if VarType(Value) = varDate then Exit;
if not VarIsNumeric(Value) then Exit;
Serial := Value;
Result := (Serial >= 60) and (Serial < 61);
end;Pass Book.Date1904 and the application's interpretation of the cell format to the predicate. When it matches, preserve the original numeric value and format instead of converting the value to TDateTime
Do not apply the 1900 rule to a 1904 workbook
Excel also has a 1904 date system, and its epoch is a workbook-level setting. A serial of 60 in a 1904 workbook is not the fictional day of the 1900 system. Read Book.Date1904 when you open the workbook and keep it with the cells being interpreted. This becomes important when copying data between workbooks: transferring the raw number preserves a serial, whereas transferring its calendar meaning requires accounting for the source and destination epochs. The two operations serve different purposes. Choose the intended behavior before you move a value, and exercise both date systems in the tests for an application that accepts workbooks from outside sources
A small boundary sample saves a long investigation
Use date-formatted cells containing 59, 59.5, 60, 60.5 and 61, plus an ordinary numeric cell containing 60. We saved and reopened a workbook containing 60.5 with HotXLS and read back the same serial. That half-day is useful because it tests the fractional time as well as the fictional day. Record the stored value, the number format and the workbook date system together when you check a round trip; otherwise a display change can be confused with a change to the underlying number. The ordinary numeric cell is an important control: the value 60 on its own can represent a quantity, and its number format supplies the context for deciding whether the special date handling is relevant. Keep that cell in the same test workbook when checking the date-formatted values
If you need a package-level check, open the worksheet XML and follow the cell's style reference into xl/styles.xml. The date-system setting belongs to the workbook rather than to an individual cell, so read that setting with the value and format. This gives a description of the file independent of the display locale on the machine opening it. If downstream business code requires a real date, the application needs an explicit policy for the fictional day: reject it, flag it for review, or carry a special value until the result is written back. Quietly converting it to February 28 discards the information needed to reconstruct the original serial
When you report the special case, include the stored serial and workbook date system. A displayed date alone can conceal whether the source held 59.5 or 60.5. The date systems and number formats guide covers the wider epoch issue, while cached formula values discusses another case where stored and displayed values deserve separate attention. The routines use the HotXLS Delphi Component