A workbook generated at 09:30 on a build machine in Shanghai opens in Excel, and File > Info reports that it was created at 17:30. The unit test that produced the file passed: it assigned 09:30, saved, reopened and read 09:30 straight back. Both readings come from the same bytes. The file holds 09:30 marked as UTC, Excel honors the marker and adds eight hours, and the test never looked at the marker at all. Document timestamps in Excel files are UTC on disk, the value a program works with is local time, and every conversion between the two has to use the daylight saving rules of the stamp's own date
The one-day error a round trip cannot see
In a .xls file the creation and save times are FILETIME values: a 64-bit count of 100-nanosecond ticks since 1601-01-01 UTC ([MS-DTYP] §2.3.3). A Delphi TDateTime counts days from 1899-12-30, the same serial origin covered in Excel date serials in Delphi and the 1900 vs 1904 systems. Converting between them needs one constant, the number of days between the two epochs, and that number is 109205. There is a quick way to check it without a calendar. The Unix epoch is 25569 as a TDateTime and 134774 days after the FILETIME epoch, and 25569 + 109205 = 134774
Count the days by hand and include both end dates, and you get 109206. A writer and reader built on that constant agree with each other perfectly. Every creation and save stamp goes into the file one day late and comes back out one day early, so a save-and-reopen test sees exactly the value it assigned, while Excel, or any reader that uses the correct epoch, shows tomorrow
const
// days from the FILETIME epoch (1601-01-01) to the TDateTime epoch (1899-12-30)
// check: 25569 + 109205 = 134774, the Unix epoch in FILETIME days
FileTimeDayBias = 109205;
function UtcDateTimeToFileTimeTicks(UtcStamp: TDateTime): Int64;
begin
// Round to whole milliseconds first, then scale to 100 ns ticks.
// Scaling the Double straight to ticks turns 04:00 into 03:59:59.9999
Result := Round((UtcStamp + FileTimeDayBias) * 86400000.0) * 10000;
end;
The rounding comment in that sketch matters too. A TDateTime fraction is a binary Double, and multiplying it directly by 864,000,000,000 ticks per day pushes the representation error into the low digits, so a stamp of exactly 04:00 reads back as 03:59:59.9999 and displays as 03:59. HotXLS rounds to whole milliseconds first and only then scales to ticks. On-the-hour values survive. The sketch takes a UTC input on purpose; the time zone step comes later and is the harder half
Which SummaryInformation property IDs hold the dates?
The \005SummaryInformation property set defined by [MS-OLEPS] keeps the creation time under property ID $0C (PIDSI_CREATE_DTM), the last-saved time under $0D (PIDSI_LASTSAVE_DTM) and the total editing time under $0A (PIDSI_EDITTIME). The ID right after the save stamp, $0E, is PIDSI_PAGECOUNT. Put a save time there and Excel has no save date to display, plus a page count property holding a FILETIME. HotXLS writes the save stamp under $0D, and when it reads a file where $0D is missing but $0E carries a VT_FILETIME, it takes that value as the last-saved time. A string or any other type sitting under one of these IDs in a malformed file is skipped rather than misread as a date. For a look at the raw streams themselves, see reading OLE2 compound files in Delphi without COM IStorage
PIDSI_EDITTIME is the odd one out. It is typed VT_FILETIME yet holds a duration: the raw count of elapsed 100 ns ticks, with no epoch added. Treat it like the other two, convert EditTimeMinutes / 1440 through the epoch maths, and 125 minutes of editing turns into a value roughly 299 years long. HotXLS writes the plain tick count (minutes × 600,000,000). On Windows the reader also catches the date-shaped encoding by its size, since no real editing session spans three centuries: any edit time of 109206 days or more has that offset subtracted before EditTimeMinutes is filled in
Setting .xls timestamps with TXLSWorkbook
TXLSWorkbook.CreatedDate and TXLSWorkbook.LastSavedDate take local TDateTime values, and HotXLS converts them to UTC FILETIMEs when the workbook is saved. RevisionNumber and EditTimeMinutes sit in the same stream. A zero value means "leave this property out", which is also how you strip all four: clear them and the next save writes none. HotXLS does not stamp Now into LastSavedDate on its own. Whatever you assign is what goes into the file, and that is the right default for a library. A batch job that regenerates last year's reports usually wants last year's dates, and an automatic stamp would quietly overwrite them
var
Book: TXLSWorkbook;
begin
Book := TXLSWorkbook.Create;
try
Book.Title := 'Q3 settlement';
// API values are local time; the file stores UTC FILETIMEs
Book.CreatedDate := EncodeDate(2026, 1, 15) + EncodeTime(9, 30, 0, 0);
Book.LastSavedDate := Now; // written as assigned, never auto-stamped
Book.RevisionNumber := 7;
Book.EditTimeMinutes := 125; // a duration, stored as raw ticks
if Book.SaveAs('settlement.xls') <> 1 then
raise Exception.Create('Save failed');
finally
Book.Free;
end;
end;
Opening a file fills the same four properties back in, converted to the local time of the machine doing the reading. A service running in UTC and a desktop in Tokyo will report different CreatedDate values for the same workbook, nine hours apart, and both are correct. If two systems need to compare stamps, convert to UTC on your side before the comparison, or store the UTC value alongside
Why are XLSX dates off by exactly the time zone offset?
XLSX dates come out off by the zone offset when a writer stamps local time and labels it UTC. In docProps/core.xml, dcterms:created and dcterms:modified are W3CDTF values under the core properties model of ECMA-376 Part 2, and the usual form ends in Z. Formatting Now and appending Z is the obvious one-liner. On a UTC+8 machine it turns 09:30 into 09:30:00Z, and Excel on that same machine shows 17:30 — the opening example, exactly. Custom date properties stored as vt:filetime follow the same rule, as do the binary FILETIMEs in .xls. HotXLS converts on all three paths before writing: TXLSXWorkbook.Created, TXLSXWorkbook.Modified and dates added through CustomProperties.AddDate are local on the API side and UTC with a Z in the file
Which Windows function does the conversion turns out to matter more than people expect. LocalFileTimeToFileTime applies the offset in effect right now. Convert a January stamp in July in any zone with daylight saving and it lands an hour off. HotXLS uses TzSpecificLocalTimeToSystemTime on write and SystemTimeToTzSpecificLocalTime on read, and both choose standard or daylight time from the date being converted, not from today. An unset value of zero is passed through untouched in both directions, so an empty property never becomes a 30 December 1899 date shifted by a few hours
var
Book: TXLSXWorkbook;
begin
Book := TXLSXWorkbook.Create;
try
if Book.Open('report-template.xlsx') <> 1 then
raise Exception.Create('Template not available');
Book.Created := EncodeDate(2026, 7, 1) + EncodeTime(9, 30, 0, 0);
Book.Modified := Now;
Book.CustomProperties.AddDate('ApprovedOn',
EncodeDate(2026, 1, 20) + EncodeTime(17, 0, 0, 0));
if Book.SaveAs('report.xlsx') <> 1 then
raise Exception.Create('Save failed');
// On a machine set to Central European Time, core.xml now holds
// <dcterms:created xsi:type="dcterms:W3CDTF">2026-07-01T07:30:00Z</dcterms:created>
// (UTC+2 in July), while ApprovedOn is written as 16:00Z (UTC+1 in January)
finally
Book.Free;
end;
end;
Outside Windows the same conversion goes through the RTL's time zone support. In Delphi that is TTimeZone.Local, and the two awkward hours of the year get a fixed answer there: a local time inside the spring-forward gap does not exist, so it is written unchanged, and an autumn hour that occurs twice is taken as the standard-time occurrence
Reading W3CDTF stamps written by other generators
Excel writes Z, other generators do not always do so, and a reader that only checks for Z at character 20 gets two common forms wrong. 2026-07-01T01:30:00.5Z has a fraction in the way, and 2026-07-01T09:30:00+08:00 has an explicit offset. A naive parser reads both as local time with no adjustment. The HotXLS reader for Created, Modified and date-valued custom properties accepts fractional seconds of any length, Z, and +hh:mm / -hh:mm offsets; it converts the instant from the given offset to UTC and then to local time. A date-only stamp such as 2026-07-01 reads as that date at midnight, and a stamp that does not parse at all comes back as zero
The one case the specifications leave open is a time with no zone marker at all, such as 2026-07-01T09:30:00. The W3CDTF profile requires a zone designator whenever a time is present, and ECMA-376 Part 2 gives no rule for a stamp that omits it. HotXLS reads such a value as local time, unchanged. Treating it as UTC would have been just as defensible on paper, but the likeliest source of a zone-less stamp is a tool that wrote local time and left the marker off, and shifting those values would move every one of that tool's timestamps by the zone offset
No reader can rescue the opposite mistake. A stamp written as local time and wearing a Z looks exactly like a correct one, so HotXLS, Excel and any other reader that follows the format shift it by the zone offset. Only the edit-time encoding and the $0E placement in .xls have a signature a reader can detect
Testing against bytes your own code did not write
Every trap above passes a save-then-reopen check, because a symmetric mistake is invisible to a symmetric test. The reference has to come from somewhere else. For .xlsx, unzip the saved file and assert the literal text of dcterms:created; for .xls, Python's olefile package reads the SummaryInformation stream independently with get_metadata(), reports the edit time in seconds and treats $0E as a page count, which makes both .xls traps visible at once. The other direction is a workbook saved by Excel itself, read through the API and compared against what File > Info shows
uses
System.SysUtils, System.DateUtils;
procedure CheckExcelReference;
var
Book: TXLSXWorkbook;
Expected: TDateTime;
begin
// The value Excel's File > Info shows for this file on the test machine
Expected := EncodeDate(2026, 1, 20) + EncodeTime(17, 0, 0, 0);
Book := TXLSXWorkbook.Create;
try
if Book.Open('excel-saved-reference.xlsx') <> 1 then
raise Exception.Create('Reference file missing');
// Your own check, not a HotXLS API: allow one second for rounding
if Abs(Book.Created - Expected) > OneSecond then
raise Exception.CreateFmt('Created reads as %s',
[DateTimeToStr(Book.Created)]);
finally
Book.Free;
end;
end;
Run that suite on a machine set to a zone well away from UTC, with one test date on each side of a daylight saving change. A build agent left on UTC hides the zone-offset error completely, and a single summer test date hides the LocalFileTimeToFileTime one; January 20 and July 1 in Central European Time catch both
Records systems, search indexes and audit trails sort by these stamps, and a date that is a day or eight hours out does more damage than a missing one, because nobody questions it. With the HotXLS Delphi spreadsheet component, the epoch constant, the property IDs and the daylight-saving-aware UTC conversion are handled for both .xls and .xlsx, so application code assigns and reads ordinary local TDateTime values. For the rest of the property set, from title and author to custom properties, the companion piece on setting Excel document properties in Delphi covers the remaining fields