Open een spreadsheet, klik op een cel die 2026-06-19 toont, en de formulebalk geeft nog steeds een datum weer. Lees dezelfde cel uit in Delphi en u krijgt het getal 46192. Beide weergaven zijn correct, omdat Excel nooit een datum in die cel heeft opgeslagen. Het sloeg een serieel getal op (een telling van dagen) en koppelde daaraan een getalnotatie die het scherm vertelt de telling als kalenderdatum weer te geven. Er is geen datumtype in de celwaarde. Er is een getal en een weergaveregel, en die regel is het enige dat een datum onderscheidt van een gewone hoeveelheid.
Die scheiding is de oorzaak van elke datumfout die een spreadsheetbibliotheek moet vermijden. Een serieel getal alleen zegt niet welke dag het is, omdat het niet aangeeft wat dag nul was. Hetzelfde getal betekent twee datums die vier jaar uit elkaar liggen, afhankelijk van een vlag in het werkboek. En een getal dat als datum zou moeten worden gelezen, wordt als een kale hoeveelheid gelezen, tenzij iets de opmaak inspecteert en een datumpatroon herkent. Dit is hoe het datummodel in HotXLS is opgebouwd, en waarom dit noodzakelijk is.
Een datumcel is een getal plus een opmaak
Excel slaat een datum op als het aantal dagen sinds een tijdperk, met de tijd in het decimale deel. Het midden van de dag in een serieel getal bevat .5. Het gehele deel is de dagtelling. Niets in de opgeslagen waarde markeert deze als tijdgebonden. Wat het markeert is de getalsopmaak van de cel: ECMA-376 noemt dit een numFmt, en een cel waarvan de opmaakcode een datum- of tijdpatroon bevat wordt weergegeven als een datum. Verwijdert u de opmaak, dan toont diezelfde cel een getal; de onderliggende waarde is nooit gewijzigd.
Dit is de reden waarom het lezen van een celwaarde u een Variant oplevert die een varDate of een gewone Double kan zijn, en waarom de getalsopmaak van die cel het signaal is dat bepaalt wat een externe partij bedoelde. Wanneer HotXLS een XLSX-bestand opent, neemt een cel zowel zijn Value als zijn NumberFormatIndex mee naar TXLSXCell, en de opmaakindex is wat u raadpleegt om te bepalen of het getal een datum is.
var
Book: TXLSXWorkbook;
Cell: TXLSXCell;
begin
Book := TXLSXWorkbook.Create;
try
if Book.Open('timesheet.xlsx') <> 1 then
raise Exception.Create('Cannot open workbook');
Cell := Book.Sheets[0].Cells[1, 1]; // row 1, col 1 (1-based)
// Value may arrive as varDate or as a plain numeric serial;
// the format index is the signal that tells them apart.
Writeln('raw value : ', VarToStr(Cell.Value));
Writeln('numFmt idx: ', Cell.NumberFormatIndex);
Writeln('format : ', Cell.NumberFormat);
finally
Book.Free;
end;
end;Twee tijdperken, 1462 dagen uit elkaar
Twee tijdperken, 1462 dagen uit elkaar
Het standaard datumsysteem, dat door elk Windows-werkboek wordt gebruikt, telt vanaf het einde van 1899, zodat serieel getal 1 op de eerste dag van 1900 valt. Het andere systeem stamt uit de vroege Macintosh-tijd en telt vanaf het begin van 1904, waardoor serieel getal 1 vier jaar en een dag later valt. Een werkboek legt in een vlag vast welk systeem het gebruikt. In een OOXML-pakket is die vlag date1904 in het werkboekdeel; HotXLS ontsluit dit als de eigenschap Date1904 van het werkboek.
Het verschil tussen de twee tijdperken is exact 1462 dagen. Dat zijn vier kalenderjaren (drie van 365 dagen en één van 366, in totaal 1461 dagen) plus één extra dag voor het kleine verschil tussen de twee dag-nul-conventies. Dit getal ligt vast. Het belang ervan is dat het niet nul is. Een serieel getal dat uit een 1904-werkboek wordt gekopieerd en volgens de 1900-regels wordt geïnterpreteerd (of omgekeerd), verschuift elke datum met 1462 dagen, wat zich presenteert als datums die iets meer dan vier jaar afwijken, wat men gemakkelijk aanziet voor corrupte gegevens.
Omdat Delphi's eigen TDateTime is verankerd aan de 1900-conventie, een bibliotheek die Excel-serials koppelt aan TDateTime moet een correctie van 1462 dagen toepassen in beide richtingen wanneer het werkboek als 1904 is gemarkeerd. Bij het lezen van een 1904-serial trekt u 1462 af voordat u deze als TDateTime behandelt; bij het schrijven van een TDateTime in een 1904-werkboek trekt u 1462 af van de serial zodat Excel de gewenste dag weergeeft. HotXLS past deze verschuiving intern toe wanneer het datumwaarden serialiseert voor een werkboek waarvan Date1904 is ingesteld, zodat de waarde die u toewijst als TDateTime correct wordt weergegeven op het scherm.
De bewuste schrikkeljaar-afwijking van 1900
Er is een bekende afwijking in het 1900-systeem. Excel behandelt 1900 als een schrikkeljaar en accepteert 29 februari 1900 als een echte datum, serieel getal 60. Het jaar 1900 was echter geen schrikkeljaar, omdat eeuwen alleen schrikkeljaren zijn als ze deelbaar zijn door 400, en dat is 1900 niet. De fantoomdag is een bewuste compatibiliteitsmaatregel die is overgenomen uit een vroege spreadsheet die deze bug bevatte. Dit is sindsdien behouden zodat seriële berekeningen identiek blijven over decennia aan bestanden.
De praktische consequentie is klein maar reëel: voor elke datum op of na 1 maart 1900 is het seriële getal één hoger dan een strikt correcte dagtelling zou opleveren, omdat het niet-bestaande 29 februari een getal in beslag nam. Een spreadsheetbibliotheek reproduceert deze afwijking in plaats van hem te herstellen, omdat het exact nabootsen van Excel's berekeningen de gehele taak is. Het corrigeren ervan zou elke moderne datum één dag laten afwijken van wat Excel toont. Dat is een slechter resultaat dan het meeslepen van een veertigduizend dagen oude off-by-one-fout die geen enkele reële datum in zakelijk gebruik raakt. Het 1900-systeem heeft geen fantoomdag, wat een reden is waarom sommige bedrijven hier historisch gezien de voorkeur aan gaven.
Een datum detecteren uit numFmt
Wanneer een getal binnenkomt uit een bestand dat door iemand anders is geschreven, is de opmaak het enige bewijs dat het om een datum gaat. ECMA-376 wijst een reeks ingebouwde opmaak-ID's toe waarvan de betekenis door de specificatie is vastgelegd, en de datum- en tijdnotaties vallen binnen bekende bereiken. ID's 14 tot en met 22 zijn de algemene datum- en tijdnotaties, zoals het bekende m/d/yyyy, h:mm en dergelijke. ID's 45 tot en met 47 zijn verstreken-tijd-notaties. Twee verdere groepen, 27 tot en met 36 en 50 tot en met 58, zijn de regiospecifieke datum- en tijdnotaties die worden gebruikt voor CJK-kalenders, gedefinieerd in ECMA-376 18.8.30. Een cel waarvan de getalsopmaak-ID binnen een van deze bereiken valt, is een datum- of tijdcel.
Ingebouwde ID's dekken de meest voorkomende gevallen, maar niet de aangepaste. Wanneer een werkboek een eigen opmaakcode definieert, bijvoorbeeld een afwijkende volgorde of een gelokaliseerde maandnaam, de ID ligt boven het ingebouwde bereik en verwijst deze naar de getalsopmaaktabel van het werkboek. Om bij deze bestanden een datum te herkennen, moet de opmaakcodestring worden gelezen en gezocht worden naar datum-tokens. HotXLS combineert beide controles in één interne functie, XlsxNumFmtIsDate, die direct true retourneert voor de ingebouwde datumbereiken en anders de aangepaste opmaakcode parseert via XlsxFormatCodeIsDate. De publieke kant hiervan zijn de NumberFormat-string en de NumberFormatIndex van de cel, die u zowel de geresolvede opmaakcode als de te testen ID opleveren.
Waarom de opmaakparser niet simpelweg op d en m kan scannen
Het parseren van een opmaakcode op datum-tokens lijkt triviaal, totdat u bedenkt wat er nog meer in een getalsopmaak kan staan. Een naïeve zoektocht naar de letters die datums spellen (de d, m, y, h en s voor dag, maand, jaar, uur en seconde) zal falen op twee structuren die helemaal geen datum-tokens zijn.
De eerste is het tekst-literal tussen aanhalingstekens. Een getalsopmaak kan letterlijke tekst tussen dubbele aanhalingstekens bevatten, zodat een financiële opmaak zoals #,##0 "MM" de tekens M en M achter een getal plaatst zonder enige temporele betekenis. Een scanner die de letters binnen de aanhalingstekens telt als maand-tokens zou die valuta-opmaak ten onrechte als datum markeren. De tweede structuur is de sectie tussen vierkante haken. Getalsopmaken bevatten instructies tussen vierkante haken, zoals kleurnamen (bijv. [Red]), vergelijkingsvoorwaarden (bijv. [>1000]), regiotags en de verstreken-tijd-markers [h] en [mm]. Sommige inhoud tussen haken bevat datumletters en andere niet, en het op gelijke wijze behandelen van tekst tussen haken als de rest van de opmaak leidt tot zowel fout-positieven als gemiste gevallen.
De juiste parser loopt karakter voor karakter door de opmaakcode, waarbij hij bijhoudt of hij zich binnen een letterlijke string bevindt en hoe diep de nesting van vierkante haken is, en respecteert ook de backslash-escape die het volgende karakter quoteert. Alleen een ontsnapt datumsymbool buiten een tekst-literal en buiten eventuele vierkante haken telt als een echt datum-token. Dat is exact hoe XlsxFormatCodeIsDate scant: een aanhalingsteken wisselt een in-literal status die de token-detectie onderdrukt tot de sluitende quote, een backslash slaat het volgende karakter over en een teller voor vierkante haken onderdrukt detectie binnen [...]-reeksen. Het resultaat is dat #,##0 "MM" correct wordt gelezen als een getalsopmaak, terwijl een beknopte aangepaste code die niets anders dan een enkele m of d buiten aanhalingstekens bevat, correct als datum wordt herkend.
Datums lezen uit bestanden van derden
Al het bovenstaande komt samen in één workflow: een getal dat door een andere applicatie is geschreven, weer omzetten in een datum die u kunt vertrouwen. De serial geeft u de dagtelling, de Date1904-vlag van het werkboek vertelt u vanaf welk epoque de telling is gemeten en de getalsopmaak-ID of aangepaste code van de cel is het enige bewijs dat het getal überhaupt als datum was bedoeld. Als u een van de drie weglaat, krijgt u een plausibel maar fout antwoord in plaats van een zichtbare foutmelding.
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
Cell: TXLSXCell;
r: Integer;
begin
Book := TXLSXWorkbook.Create;
try
if Book.Open('vendor-export.xlsx') <> 1 then
raise Exception.Create('Cannot open export');
// The 1904 flag is workbook-wide: read it once, apply it to
// every serial the workbook hands back.
if Book.Date1904 then
Writeln('workbook uses the 1904 date system')
else
Writeln('workbook uses the 1900 date system');
Sheet := Book.Sheets[0];
for r := 1 to 10 do
begin
Cell := Sheet.Cells[r, 1];
// A date is only a date when its format says so; the same numeric
// value with a plain format is just a quantity.
Writeln(Format('row %d value=%s numFmt=%d code="%s"',
[r, VarToStr(Cell.Value), Cell.NumberFormatIndex, Cell.NumberFormat]));
end;
finally
Book.Free;
end;
end;De legacy BIFF-kant heeft nog een extra valkuil die het vermelden waard is. In een oudere .xls-stream kan een reeks aangrenzende numerieke cellen worden verpakt in één enkel multi-celrecord, de MULRK, dat meerdere waarden met hun opmaakverwijzingen in één structuur opslaat. Datumcellen die op die manier zijn opgeslagen zijn niet minder datums omdat ze zijn verpakt, dus dezelfde opmaak-ID-test moet per cel worden uitgevoerd en de 1904-verschuiving is nog steeds van toepassing op elke serial die het oplevert. Een lezer die alleen zelfstandige getalrecords inspecteert en de verpakte records overslaat, verandert een kolom met datums geruisloos in een kolom met gehele getallen.
Serials koppelen aan TDateTime in de praktijk
Zodra de opmaakcontrole een datum bevestigt en de Date1904-vlag bekend is, is de conversie mechanisch. Een waarde die HotXLS al als een varDate retourneert, is een TDateTime die u rechtstreeks kunt gebruiken. Een waarde die binnenkomt als een kale Double (wat gebeurt wanneer de bron een serial heeft geschreven zonder herkende datumopmaak), wordt geconverteerd door deze te lezen als een dagtelling op de 1900-as, waarbij voor een 1904-werkboek eerst de verschuiving van 1462 dagen wordt afgetrokken zodat de epoques overeenstemmen. Andersom slaat het toewijzen van een TDateTime aan een cel de 1900-gebaseerde serial op. HotXLS past dezelfde verschuiving van 1462 dagen toe bij het opslaan wanneer de Date1904-vlag van het werkboek is ingesteld, zodat het opgeslagen bestand de datum toont die u bedoelde, in plaats van een datum die vier jaar afwijkt.
Stel de vlag bewust in wanneer u een werkboek genereert. De standaardwaarde laat Date1904 onwaar, wat overeenkomt met Excel voor Windows en bijna altijd is wat u wilt; stel het alleen in op waar als u een van Mac afkomstig werkboek reproduceert of als een downstreamsysteem specifiek de 1904-as verwacht. De enige regel die de hele categorie van vierjaarsfouten voorkomt is consistentie: kies het epoque eenmaal per werkboek, schrijf elke datum daaronder en lees elke serial terug onder de vlag die het bestand daadwerkelijk draagt.
Dates are one column in a wider story about what a cell really holds. The neighboring metadata layer, the title and author and timestamps that ride alongside the grid, is covered in our article on workbook metadata and document properties, where the same Created and Modified values are stored as TDateTime with the same unset-equals-zero convention. When a date is the result of a calculation rather than a stored value, the evaluation rules in our article on the formula engine and custom functions determine the serial that the format then renders. Both work over the same date model that ships in the HotXLS Component for Delphi and C++Builder, which reads and writes XLS and XLSX dates without Excel automation.