Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Date/Time and NumberFormat issue
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Date/Time and NumberFormat issue
Paul wrote in
48.16: 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! Well I know that's because of 29 feb 1900... but still "Argh!". And that doesn't help with my original problem. Paul |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date and Time Picker issue | Excel Discussion (Misc queries) | |||
Date and Time Picker - two an issue? | Excel Discussion (Misc queries) | |||
Date/Time issue with Pivot Table | Excel Discussion (Misc queries) | |||
Combined date time cell to separate date & time components | Excel Discussion (Misc queries) | |||
Combined date time cell to separate date & time components | Excel Discussion (Misc queries) |