Serijski brojevi datuma u Excelu u Delphi-ju: 1900 naspram 1904 i numFmt

Otvorite proračunsku tablicu, kliknite na ćeliju koja prikazuje 2026-06-19, i traka formule i dalje čita datum. Pročitajte istu ćeliju iz Delphi-ju i dobit ćete broj 46192. Oba prikaza su točna, jer Excel nikada nije pohranio datum u tu ćeliju. Pohranio je serijski broj, broj dana, i priložio oblik broja koji govori zaslonu da prikaže taj broj kao kalendarski datum. U vrijednosti ćelije nema tipa datuma. Postoji broj i pravilo prikaza, a pravilo prikaza je jedina stvar koja razlikuje datum od obične količine

To razdvajanje je korijen svakog buga s datumom koji knjižnica za proračunske tablice mora izbjeći. Sam serijski broj ne govori koji je dan, jer ne govori što je bio nulti dan. Isti broj označava dva datuma s razmakom od četiri godine ovisno o jednoj zastavici radne knjige. A broj koji bi se trebao pročitati kao datum pročitat će se kao obična količina osim ako nešto ne pregleda njegov format i prepozna uzorak datuma. Tako je izgrađen model datuma u HotXLS-u, i to s razlogom

Ćelija s datumom je broj plus format

Excel pohranjuje datum kao broj dana od epohe, s vremenom dana u decimalnom dijelu. Podne na serijskom broju nosi .5. Cjelobrojni dio je broj dana. Ništa u pohranjenoj vrijednosti ne označava je kao vremensku. Ono što je označava je format broja ćelije: ECMA-376 to naziva numFmt, a ćelija čiji format koda ispisuje uzorak datuma ili vremena prikazuje se kao datum. Skinite format i ista ćelija prikazuje broj; temeljna vrijednost se nikada nije promijenila

Zbog toga čitanje vrijednosti ćelije daje Variant koji može biti varDate ili običan Double, i zašto je format broja na istoj ćeliji signal koji odlučuje što je treća strana mislila. Kada HotXLS otvori XLSX datoteku, ćelija prenosi i svoju vrijednost Value i svoj indeks formata broja NumberFormatIndex u TXLSXCell, a indeks formata je ono što konzultirate kako biste saznali je li broj datum

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];   // redak 1, stupac 1 (broji se od 1)
    // Vrijednost može stići kao varDate ili kao obični numerički serijski broj;
    // indeks formata je signal koji ih razlikuje.
    Writeln('raw value : ', VarToStr(Cell.Value));
    Writeln('numFmt idx: ', Cell.NumberFormatIndex);
    Writeln('format    : ', Cell.NumberFormat);
  finally
    Book.Free;
  end;
end;

Dvije epohe, s razmakom od 1462 dana

Zadani sustav datuma, onaj koji koristi svaka radna knjiga na Windowsima, broji od samog kraja 1899. godine, tako da serijski broj 1 falls on the first day of 1900. Drugi sustav potječe s ranog Macintosh računala i broji od početka 1904. godine, tako da je njegov serijski broj 1 četiri godine i jedan dan kasnije. Radna knjiga bilježi koji sustav koristi u jednoj zastavici. U OOXML paketu ta je zastavica date1904 na dijelu radne knjige; HotXLS je prikazuje kao svojstvo Date1904 radne knjige

Razmak između dviju epoha je točno 1462 dana. To su četiri kalendarske godine, tri od 365 dana i jedna od 366 dana, ukupno 1461 dan, plus još jedan dan za pomak između dviju konvencija nultog dana. Broj je fiksan i možete ga nositi u glavi. Njegova važnost je u tome što nije nula. Serijski broj kopiran iz radne knjige iz 1904. i interpretiran pod pravilima iz 1900., ili obrnuto, smješta svaki datum 1462 dana dalje, što se predstavlja kao datumi koji su pogrešni za nešto više od četiri godine i lako se može zamijeniti za oštećene podatke

Budući da je Delphi-jev vlastiti TDateTime usidren na konvenciju iz 1900. godine, knjižnica koja mapira Excel serijske brojeve na TDateTime mora napraviti pomak za 1462 u oba smjera kad god radna knjiga ima zastavicu 1904. Čitajući serijski broj iz 1904., oduzmite 1462 prije nego što ga tretirate kao TDateTime; pišući TDateTime u radnu knjigu iz 1904., oduzmite 1462 od serijskog broja kako bi Excel prikazao dan koji ste zamislili. HotXLS primjenjuje ovaj pomak interno kada serijalizira vrijednosti datuma za radnu knjigu čiji je Date1904 postavljen, tako da se vrijednost koju dodijelite kao TDateTime vraća na isti kalendarski dan na zaslonu

Namjerna neobičnost prijestupne godine 1900

Postoji poznata neobičnost u sustavu 1900. Excel tretira 1900. kao prijestupnu godinu i prihvaća 29. veljače 1900. kao stvarni datum, serijski broj 60. Godina 1900. nije bila prijestupna godina, jer su sekularne godine prijestupne samo kada su djeljive s 400, a 1900. nije. Fantomski dan je namjerno kompatibilno ponašanje naslijeđeno iz rane proračunske tablice koja je isporučena s tim bugom, zadržano od tada kako bi serijska aritmetika ostala identična kroz desetljeća datoteka

Praktična posljedica je mala, ali stvarna: za bilo koji datum na dan ili nakon 1. ožujka 1900. godine, serijski broj je za jedan veći nego što bi dala strogo točna raspodjela dana, jer je nepostojeći 29. veljače potrošio broj. Knjižnica proračunskih tablica reproducira tu neobičnost umjesto da je ispravlja, jer je usklađivanje s Excelovom aritmetikom cijeli posao. Njezino ispravljanje stavilo bi svaki moderni datum jedan dan dalje od onoga što Excel prikazuje, što je lošiji ishod nego nošenje četrdeset tisuća dana starog odstupanja za jedan koje nijedan stvarni datum u poslovnoj uporabi nikada ne dotiče. Sustav 1904 nema ekvivalentan fantomski dan, što je jedan od razloga zašto su mu neke tvrtke povijesno davale prednost

Otkrivanje datuma iz numFmt

Kada broj stigne iz datoteke koju je napisao netko drugi, njegov format je jedini dokaz da se radi o datumu. ECMA-376 dodjeljuje blok ugrađenih ID-ova formata čije je značenje fiksirano specifikacijom, a formati datuma i vremena zauzimaju poznate raspone. ID-ovi od 14 do 22 su opći lokalni formati datuma i vremena, poznati m/d/yyyy, h:mm i njihovi srodnici. ID-ovi od 45 do 47 su formati proteklog vremena. Još dva pojasa, od 27 do 36 i od 50 do 58, su specifični lokalni formati datuma i vremena koji se koriste za CJK kalendare, definirani u ECMA-376 18.8.30. Ćelija čiji ID formata broja spada u bilo koji od ovih raspona je ćelija datuma ili vremena

Ugrađeni ID-ovi pokrivaju uobičajene slučajeve, ali ne i prilagođene. Kada radna knjiga definira vlastiti kod formata, recimo nestandardni redoslijed ili lokalizirani naziv mjeseca, ID je iznad ugrađenog raspona i upućuje na tablicu formata brojeva radne knjige. Za njih prepoznavanje datuma znači čitanje niza koda formata i traženje tokena datuma. HotXLS spaja obje provjere u jedan interni predikat, XlsxNumFmtIsDate, koji odmah vraća true za ugrađene raspone datuma, a inače analizira prilagođeni kod formata kroz XlsxFormatCodeIsDate. Javno dostupna strana toga je niz NumberFormat ćelije i njezin indeks NumberFormatIndex, koji vam daju i razriješeni kod formata i ID koji trebate testirati

Zašto parser formata ne može samo tražiti d i m

Analiza koda formata za tokene datuma izgleda jednostavno dok se ne sjetite što još živi u formatu broja. Naivna potraga za slovima koja označavaju datume, d, m, y, h i s za dan, mjesec, godinu, sat i sekundu, pogrešno će se aktivirati na dvjema strukturama koje uopće nisu tokeni datuma

Prvi je literal citiranog niza. Format broja može ugraditi literalni tekst u dvostruke navodnike, pa financijski format poput #,##0 "MM" dodaje znakove M i M broju bez ikakvog vremenskog značenja. Skener koji broji slova unutar navodnika kao tokene mjeseca pogrešno bi označio taj valutni format kao datum. Drugi je odjeljak u zagradama. Formati brojeva nose direktive u uglatim zagradama, nazive boja poput [Red], uvjete usporedbe poput [>1000], lokalne oznake i markere proteklog vremena [h] i [mm]. Neki sadržaji u zagradama drže slova datuma, a neki ne, a tretiranje teksta u zagradama isto kao i tijela formata dovodi i do lažno pozitivnih rezultata i do propuštenih slučajeva

Ispravan parser prolazi kroz kod formata znak po znak, prateći nalazi li se unutar citiranog literala i koliko je duboko unutar ugniježđenih zagrada, a također poštuje i kosu crtu (backslash) koja citira jedan sljedeći znak. Samo neizbježno slovo datuma pronađeno izvan bilo kojeg znakovnog literala i izvan bilo kojeg odjeljka u zagradi računa se kao stvarni token datuma. To je upravo način na koji skenira XlsxFormatCodeIsDate: navodnik prebacuje stanje unutar literala koje potiskuje otkrivanje tokena do zatvarajućeg navodnika, kosa crta preskače sljedeći znak, a brojač dubine zagrada potiskuje otkrivanje unutar [...] segmenata. Rezultat je da se #,##0 "MM" ispravno čita kao format broja, dok se kratki prilagođeni kod koji ne sadrži ništa osim jednog m ili d izvan navodnika i dalje ispravno prepoznaje kao datum

Čitanje datuma iz datoteka trećih strana

Sve gore navedeno konvergira na jedan tijek rada: pretvaranje broja koji je napisala neka druga aplikacija natrag u datum kojem možete vjerovati. Serijski broj daje vam broj dana, zastavica radne knjige Date1904 govori iz koje se epohe mjeri broj, a ID formata broja ćelije ili prilagođeni kod jedini je dokaz da je broj uopće bio namijenjen kao datum. Ispustite bilo koji od ova tri elementa i dobit ćete uvjerljiv pogrešan odgovor umjesto vidljive pogreške

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');

    // Zastavica 1904 vrijedi za cijelu radnu knjigu: pročitajte je jednom
    // i primijenite na svaki serijski broj koji radna knjiga vrati.
    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];
      // Datum je datum samo kada to kaže njegov format; ista numerička
      // vrijednost s običnim formatom je samo količina.
      Writeln(Format('row %d  value=%s  numFmt=%d  code="%s"',
        [r, VarToStr(Cell.Value), Cell.NumberFormatIndex, Cell.NumberFormat]));
    end;
  finally
    Book.Free;
  end;
end;

Naslijeđena BIFF strana ima jednu dodatnu zamku vrijednu spomena. U starijem .xls toku, niz susjednih numeričkih ćelija može se spakirati u jedan zapis s više ćelija, MULRK, koji pohranjuje nekoliko vrijednosti sa svojim referencama formata u jednoj strukturi. Stanice s datumima pohranjene na taj način nisu manje datumi zbog toga što su spakirane, pa isti test ID formata mora dosegnuti unutar zapisa s više ćelija i primijeniti se po ćeliji, a pomak iz 1904. i dalje vlada svakim serijskim brojem koji daje. Čitač koji pregledava samo samostalne zapise brojeva, a preskače spakirane, tiho će pretvoriti stupac datuma u stupac cijelih brojeva

Pretvaranje serijskih brojeva u TDateTime u praksi

Jednom kada provjera formata potvrdi datum i kada je poznata zastavica Date1904, pretvorba je mehanička. Vrijednost koju HotXLS već vraća kao varDate je TDateTime koji možete izravno koristiti. Vrijednost koja stiže kao običan Double, što se događa kada je izvor zapisao serijski broj bez prepoznatog formata datuma, pretvara se čitanjem kao broj dana na osi 1900 i, za radnu knjigu iz 1904., prvim oduzimanjem pomaka od 1462 dana kako bi se epohe uskladile. Idući drugim putem, dodjeljivanje TDateTime ćeliji pohranjuje serijski broj temeljen na 1900., a HotXLS primjenjuje isti pomak od 1462 dana pri spremanju kada radna knjiga ima zastavicu 1904., tako da spremljena datoteka prikazuje datum koji ste namjeravali, a ne onaj koji pluta četiri godine dalje

Postavite zastavicu namjerno kada generirate radnu knjigu. Zadana vrijednost ostavlja Date1904 netočnim, što odgovara Excelu za Windowse i gotovo je uvijek ono što želite; postavite je na točno samo kada reproducirate radnu knjigu podrijetlom s Maca ili kada nizvodni sustav izričito očekuje os 1904. Jedno pravilo koje sprječava cijelu klasu četverogodišnjih pogrešaka je dosljednost: odaberite epohu jednom po radnoj knjizi, zapišite svaki datum pod njom i pročitajte svaki serijski broj pod zastavicom koju datoteka stvarno nosi

Datumi su jedan stupac u široj priči o tome što ćelija doista sadrži. Susjedni sloj metapodataka, naslov, autor i vremenske oznake koji putuju uz mrežu, pokriven je u našem članku o metapodacima radne knjige i svojstvima dokumenta, gdje se iste vrijednosti Created i Modified pohranjuju kao TDateTime s istom konvencijom nepodešeno-jednako-nula. Kada je datum rezultat izračuna, a ne pohranjena vrijednost, pravila procjene u našem članku o pogonu formula i prilagođenim funkcijama određuju serijski broj koji format zatim prikazuje. Obje rade na istom modelu datuma koji se isporučuje u softveru HotXLS Delphi Component za Delphi i C++Builder, koji čita i piše Excel XLS i XLSX datume bez Excel automatizacije