[excel] Probleem met datum & tijdstip

[Amafi]

Kwam bij het volgende probleem door het volgende.

Voor een analyse moet ik de uren in een jaar overzichtelijk onder elkaar zetten, met de bijbehorende datum erbij.
Geen probleem, cel eigenschappen d-m-jjjj u:mm geeft een helder overzicht.
Omdat ik lui ben kopiëerde ik deze naar beneden, maar wat schetst mijn verbazing in excel 2000 en 2003 staat er bij rij 101 opeens in plaats van hele uren. 5-1-2007 3:59

En bij rij 12101 schiet die naar: 19-5-2008 3:58:59
Wat ik natuurlijk wil is ook op rij 12101 19-5-2008 4:00

Weet iemand waar het fout gaat, of hoe ik de tijden kan corrigeren?
Misschien het makkelijkst te reproduceren zoals pedorus meldde.

Klein voorbeeld
1-1-2007 0:00
1-1-2007 1:00
1-1-2007 2:00
…….. (knip de tussenliggende waarden weg, alles staat op x.00 uren in ieder geval)
5-1-2007 2:00
5-1-2007 3:00
5-1-2007 3:59 <- echt waarom?
5-1-2007 4:59

[ Voor 30% gewijzigd door Amafi op 11-12-2008 22:08 ]

[Anoniem: 147180]

Heb je een voorbeeldbestand?

[pedorus]

Ik kende dit probleem nog niet, maar kan het wel reproduceren. Je begint dus in rij 1 met 1-1-2007 0:00, en in rij 2 met 1-1-2007 1:00. Als je dan doortrekt, dan krijg je dit. Dit komt doordat Excel intern datums als doubles ziet in zo'n geval, en er nu foutief wordt afgerond... Een mogelijke oplossing is om de volgende formule door te trekken:

code:

=DATE(2007,1,1+(ROW(A1)-1)/24)+TIME(ROW(A1)-1,0,0)

[ Voor 18% gewijzigd door pedorus op 11-12-2008 22:06 ]

[Amafi]

pedorus schreef op donderdag 11 december 2008 @ 22:02:
Ik kende dit probleem nog niet, maar kan het wel reproduceren. Je begint dus in rij 1 met 1-1-2007 0:00, en in rij 2 met 1-1-2007 1:00. Als je dan doortrekt, dan krijg je dit. Dit komt doordat Excel intern datums als doubles ziet in zo'n geval, en er nu foutief wordt afgerond... Een mogelijke oplossing is om de volgende formule door te trekken:

code:

=DATE(2007,1,1+(ROW(A1)-1)/24)+TIME(ROW(A1)-1,0,0)

Lijkt bij mij niet zo goed te werken. Ik krijg dan telkens een verschuiving ter grootte van een dag in plaats van op uur nivo?
Kan dat nog liggen aan celeigenschappen?

[pedorus]

Amafi schreef op donderdag 11 december 2008 @ 22:15:
[...]

Lijkt bij mij niet zo goed te werken. Ik krijg dan telkens een verschuiving ter grootte van een dag in plaats van op uur nivo?
Kan dat nog liggen aan celeigenschappen?

Denk het. Gebruik Format Cells...->Custom->dd-mm-yyyy hh:mm . Als je dd-mm-yyyy hh:mm:ss doet, zie je bij je oorspronkelijke probleem het zelfs op secondeniveau misgaan (eerste seconde afrondingsfout bij rij 101).

[Amafi]

pedorus schreef op donderdag 11 december 2008 @ 22:22:
[...]

Denk het. Gebruik Format Cells...->Custom->dd-mm-yyyy hh:mm . Als je dd-mm-yyyy hh:mm:ss doet, zie je bij je oorspronkelijke probleem het zelfs op secondeniveau misgaan (eerste seconde afrondingsfout bij rij 101).

Ah, laat maar heb het gevonden:

code:

=DATE(2007,1,1+(ROW(A1)-1[b])[/b]/24)+TIME(ROW(A1)-1,0,0)

Was dat klote haakje vergeten. Moest het even overtypen op mijn werklaptop die niet op publiek internet kan. Maar die accepteerde het niet lekker zonder haakje.

Mijn dank overigens
Toch wel vreemd dat een ogenlijk simpele excel bewerking zo omslachtig gemaakt moet worden om feilloos te werken.
Maar ik kan weer wat collega's verblijden met deze uitkomst!

[Amafi]

Heb de formule toch nog wat aangepast naar een simpelere variant:

code:

A1       1/1/07 0:00
A2       =A2+1/24
A3       =A3+1/24
enz.

[Lustucru]

In plaats van met formules te werken kun je ook eerst in opties, berekenen, precisie zoals afgebeeld aanvinken en dan de lijst doorvoeren. Rondt ie af op hele minuten/seconden.

edit:
en daarna weer aanzetten

[ Voor 10% gewijzigd door Lustucru op 12-12-2008 12:51 ]

[pedorus]

Lustucru schreef op vrijdag 12 december 2008 @ 12:08:
In plaats van met formules te werken kun je ook eerst in opties, berekenen, precisie zoals afgebeeld aanvinken en dan de lijst doorvoeren. Rondt ie af op hele minuten/seconden.

Dat heeft enkel behoorlijke side-effects voor de hele workbook - uit de help:

Precision as displayed Permanently changes stored values in cells from full precision (15 digits) to whatever format, including decimal places, is displayed.

En je krijgt ook een extra warning.

Gek trouwens dat het afrondingsprobleem lijkt te zitten in het berekenen van het verschil tussen twee cellen bij doortrekken, en niet in de nauwkeurigheid per cel. Je kan dus ook in de cel op rij 1 "1-1-2007 0:" invoeren, de kolom selecteren, en dan edit->fill->series...->step value=0.0416666666666667 doen.