Spring naar hoofdtekst

Verloren seconde: Excel vs. Calc

Geplaatst op door .
Laatste aanpassing op .

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… :-)

Terug naar boven

Inhoudsopgave

Delen

Met onderstaande koppelingen kunt u deze pagina delen via sociale media of e-mail.

Atom-feed van FWiePs weblog

Artikelen


Categorieën

Doorzoek de onderstaande categorieën om de lijst met artikelen te filteren.


Terug naar boven