Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel97: Fillup cells with ascending timestamps
Hi,
I have to fillup cells by VBA with hourly timestamps, formatted as "DD.MM.YYYY HH" in ascending order. I use the function dNew = DateAdd("h", 1, dTimeCount) where dTimeCount is the old timestamp. The cells in the worksheet are formatted with "TT.M.JJJJ hh". When the day changes something is wrong: ..... 22.01.2004 21 22.01.2004 22 22.01.2004 23 22.01.2004 00 <--- 23.01.2004 01 23.01.2004 02 ..... Instead of 23.01.2004 00<< the cell value is 22.01.2004 00<< !? Bug or feature ? And how to deal with this ? Thanks Holger |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel97: Fillup cells with ascending timestamps
-----Original Message----- Hi, I have to fillup cells by VBA with hourly timestamps, formatted as "DD.MM.YYYY HH" in ascending order. I use the function dNew = DateAdd("h", 1, dTimeCount) where dTimeCount is the old timestamp. The cells in the worksheet are formatted with "TT.M.JJJJ hh". When the day changes something is wrong: ..... 22.01.2004 21 22.01.2004 22 22.01.2004 23 22.01.2004 00 <--- 23.01.2004 01 23.01.2004 02 ..... Instead of 23.01.2004 00<< the cell value is 22.01.2004 00<< !? Bug or feature ? And how to deal with this ? Thanks Holger . Can't really say if it would be considered a bug or a feature (ask someone from MS) - but basically it is counting midnight, 00:00:00, as part of the day prior. I suspect this is a rounding error. If it does not adversely affect any other part of your calculations, try adding one second (00:00:01) to your timestamps - then "midnight" will actually show as one second past midnight, which I would hope Excel could recognize as being in the following day! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel calcutions using date/timestamps | Excel Discussion (Misc queries) | |||
Sort ascending requires merged cells to be the same size. | Excel Discussion (Misc queries) | |||
How to decode integer timestamps? | Excel Worksheet Functions | |||
timestamps in excel | Excel Discussion (Misc queries) | |||
Formatting cells in Excel with Ascending/Descending order | Excel Discussion (Misc queries) |