Inleiding
Al enige tijd maak ik dankbaar gebruik van PhpSpreadsheet, een project om
comfortabel te werken met spreadsheets in PHP. Het ondersteunt verschillende
bestandsformaten voor inlezen en uitvoer, zoals .XLSX (Microsoft) en .ODS
(OpenDocument).
Als fervent voorstander van opensource software, gebruik ik onder andere GNU/Linux als besturingssysteem met LibreOffice als kantoorpakket. Met laatstgenoemde was echter iets bijzonders aan de hand: er leek in Calc (de spreadsheet-component) één seconde verschil in weergave van een datum + tijd-kolom, ten opzichte van dezelfde kolom in Microsoft Excel onder Windows of Android.
Probleem
In de door mijn code gegenereerde spreadsheet was één kolom gevuld met een
geformatteerde datum en tijd. Er stond bijvoorbeeld zo 31-07-2022 10:59. Maar
ik wist zeker dat in mijn applicatie (en de achterliggende database) een tijd
van 11:00 stond ingevoerd. Ook halve uren waren nét te kort: 9:30 in mijn
applicatie werd 09:29 in Calc. Wat was hier aan de hand?
Oorzaak
Aldus ging ik op zoek en verdiepte me in de formattering van datum en tijd binnen Excel en Calc. Het vrije programma bleek zo goed als 100% compatibel met het product uit Redmond, dus daar mocht het niet aan liggen. Pas toen ik ontdekte hoe Excel achter de schermen met datums en tijden werkt, snapte ik wat er gebeurde: ze worden opgeslagen als decimaal getal met de datum vóór, en de tijd achter de komma.
zo 24-07-2022 09:29 => 44766,395833333
Een dag heeft 24 uur, elk uur 60 minuten, elk uur 60 seconden. Eén dag heeft dus
24 × 60 × 60 = 86400 seconden. Om in Excel (en Calc) een tijd
te formatteren met een preciesie van één seconde, moet je dus met eenheden van
(1 / 86400) = 0,00001157407407407410 werken. Voor een precisie
van één minuut zal dit (1 / 1440) = 0,00069444444444444400 zijn.
De tijd-kolom moet dus ook een minimum aantal decimalen hebben om correct te
kunnen formatteren.
Toen ik in dit voorbeeld van de laatste 3 een 4 maakte, versprong de
geformatteerde tijd (minuut) naar de gewenste waarde. Blijkbaar rondt Excel bij
het formateren van tijd naar boven af, en Calc niet.
44766,395833334 => zo 24-07-2022 09:30
Oplossing
Mijn relatief eenvoudige oplossing was, om een tweede datum-tijd-kolom aan de
spreadsheet toe te voegen. Deze gaf ik dezelfde opmaak (formattering) als de
bestaande kolom: DDD DD-MM-JJJJ UU:MM met de Nederlandse regio-instellingen.
In de cel plaatste ik vervolgens deze formule (voorbeeld rij 2):
=ROUNDUP(B2; 5)
Hierdoor werd elke tijd naar boven afgerond op 5 decimalen. Dit was voldoende om de weergave op minuten te laten kloppen. Tot slot verborg ik nog de oude kolom en klaar!
P.S: Ik had nog getwijfeld of ik de getoonde formule niet direct zou integreren in mijn PHP code die de spreadsheet genereert. Maar dan zou die kolom alleen deze formule en het decimale getal bevatten; niet bepaald handig om te kopiëren en plakken om daarna aan te passen. Een mens rekent nou eenmaal niet zo makkelijk met cijfers… :-)