P
Paul
Hi everyone,
I seem to have come across something rather unpleasant in Excel involving
the display of Dates (incl. time component). in cells. Basically if there
is a fraction of a second in the value so that it is more than about 1e-16
beneath a whole day but less than half a second the displayed time comes
out wrong.
Because Dates get manipulated as fp numbers there's a huge risk of this
happening.
Here's some example code of it happening by adding up lots of units of 10
seconds
Sub problem()
Sheet1.Range("A1").NumberFormat = "[hh]:mm:ss dd/mm/yyyy"
Sheet1.Range("A1") = TimeSerial(0, 0, 1)
Dim z As Date
For i = 1 To 8640
z = DateAdd("s", 10, z)
Next
Sheet1.Range("A2").NumberFormat = "[hh]:mm:ss dd/mm/yyyy"
Sheet1.Range("A2") = z
Sheet1.Range("A3").NumberFormat = "[hh]:mm:ss dd/mm/yyyy"
Sheet1.Range("A3") = CDbl(z)
Sheet1.Range("A4").NumberFormat = "General"
Sheet1.Range("A4") = CDbl(z)
Sheet1.Range("A5") = CDbl(Sheet1.Range("A2"))
End Sub
The output in the sheet is
00:00:01 00/01/1900
00:00:00 00/01/1900
24:00:00 01/01/1900
1
0
I put cell A1 is as an example of what the format looks like normally. The
value in cell A2 appears to be zero rather than near 1!
When I go edit the "24:00:00 01/01/1900" line it displays in the edit box
at the top as "00/01/1900 00:00:00"
z-1 actually evaluates to -1.15463194561016E-13
TimeValue(z) shows up as 00:00:00 though
If the loop goes round one fewer times you get the rather more sensible
looking (well apart from the date being the zeroth of Jan)
00:00:01 00/01/1900
23:59:50 00/01/1900
23:59:50 00/01/1900
0.999884259
0.999884259
One more time and you get the also (mostly) sensible
00:00:01 00/01/1900
24:00:10 01/01/1900
24:00:10 01/01/1900
1.000115741
1.000115741
This is by no means the only way of getting it to happen, I noticed this
adding up far fewer numbers (but you have to be "unlucky" whereas the
example works every time on Excel 2002). As you can see from A5 it isn't
just the displayed string that's off - the underlying value seems to get
changed, so writing and reading the cell back can yield wildly differing
answers. You can also provoke this sort of thing by doing arithmetic on a
date. I also notice that the formatted value seems to discard the
fractional part. Adding up 8640 lots of 10s on a spreadsheet works fine
though.
Things like CDate(1 - (TimeSerial(0, 0, 1) / 4)) also make it very unhappy
showing up as "00:00:00".
Using the VBA Format() function with the date doesn't seem to cause any of
these problems. But starts counting at 30/12/1899 and at some point later
on, the values between the two formats become the same.
I'm sure Excel 2000 didn't have this difference between the two, but I
don't have it handy to check
Sheet1.Range("A1") = CDate("31/12/1899")
Debug.Print (Sheet1.Range("A1").Text)
Debug.Print (Sheet1.Range("A1").Value)
Yields
01/01/1900
31/12/1899
Argh!
Feeding something like "00/01/1900" to CDate makes it spit it out in
disgust of course
Why am I messing around with such early dates you ask? The answer is I'm
not. I'm using times, which Excel represents as dates at the start of the
calendar and adding the times up, which I want displayed as
hours/minutes/seconds.
But given how freely VB converts between doubles, dates and strings, I'm
now downright terrified of the number of places were the wrong data might
be showing up on Excel spreadsheets that use VBA.
Am I going mad here? Is there some really nice easy answer to all this that
I'm missing?
Thanks in advance,
Paul
I seem to have come across something rather unpleasant in Excel involving
the display of Dates (incl. time component). in cells. Basically if there
is a fraction of a second in the value so that it is more than about 1e-16
beneath a whole day but less than half a second the displayed time comes
out wrong.
Because Dates get manipulated as fp numbers there's a huge risk of this
happening.
Here's some example code of it happening by adding up lots of units of 10
seconds
Sub problem()
Sheet1.Range("A1").NumberFormat = "[hh]:mm:ss dd/mm/yyyy"
Sheet1.Range("A1") = TimeSerial(0, 0, 1)
Dim z As Date
For i = 1 To 8640
z = DateAdd("s", 10, z)
Next
Sheet1.Range("A2").NumberFormat = "[hh]:mm:ss dd/mm/yyyy"
Sheet1.Range("A2") = z
Sheet1.Range("A3").NumberFormat = "[hh]:mm:ss dd/mm/yyyy"
Sheet1.Range("A3") = CDbl(z)
Sheet1.Range("A4").NumberFormat = "General"
Sheet1.Range("A4") = CDbl(z)
Sheet1.Range("A5") = CDbl(Sheet1.Range("A2"))
End Sub
The output in the sheet is
00:00:01 00/01/1900
00:00:00 00/01/1900
24:00:00 01/01/1900
1
0
I put cell A1 is as an example of what the format looks like normally. The
value in cell A2 appears to be zero rather than near 1!
When I go edit the "24:00:00 01/01/1900" line it displays in the edit box
at the top as "00/01/1900 00:00:00"
z-1 actually evaluates to -1.15463194561016E-13
TimeValue(z) shows up as 00:00:00 though
If the loop goes round one fewer times you get the rather more sensible
looking (well apart from the date being the zeroth of Jan)
00:00:01 00/01/1900
23:59:50 00/01/1900
23:59:50 00/01/1900
0.999884259
0.999884259
One more time and you get the also (mostly) sensible
00:00:01 00/01/1900
24:00:10 01/01/1900
24:00:10 01/01/1900
1.000115741
1.000115741
This is by no means the only way of getting it to happen, I noticed this
adding up far fewer numbers (but you have to be "unlucky" whereas the
example works every time on Excel 2002). As you can see from A5 it isn't
just the displayed string that's off - the underlying value seems to get
changed, so writing and reading the cell back can yield wildly differing
answers. You can also provoke this sort of thing by doing arithmetic on a
date. I also notice that the formatted value seems to discard the
fractional part. Adding up 8640 lots of 10s on a spreadsheet works fine
though.
Things like CDate(1 - (TimeSerial(0, 0, 1) / 4)) also make it very unhappy
showing up as "00:00:00".
Using the VBA Format() function with the date doesn't seem to cause any of
these problems. But starts counting at 30/12/1899 and at some point later
on, the values between the two formats become the same.
I'm sure Excel 2000 didn't have this difference between the two, but I
don't have it handy to check
Sheet1.Range("A1") = CDate("31/12/1899")
Debug.Print (Sheet1.Range("A1").Text)
Debug.Print (Sheet1.Range("A1").Value)
Yields
01/01/1900
31/12/1899
Argh!
Feeding something like "00/01/1900" to CDate makes it spit it out in
disgust of course
Why am I messing around with such early dates you ask? The answer is I'm
not. I'm using times, which Excel represents as dates at the start of the
calendar and adding the times up, which I want displayed as
hours/minutes/seconds.
But given how freely VB converts between doubles, dates and strings, I'm
now downright terrified of the number of places were the wrong data might
be showing up on Excel spreadsheets that use VBA.
Am I going mad here? Is there some really nice easy answer to all this that
I'm missing?
Thanks in advance,
Paul