Excel97: Fillup cells with ascending timestamps

  • Thread starter Thread starter Holger Osterbuhr
  • Start date Start date
H

Holger Osterbuhr

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
Bug or feature ? And how to deal with this ?

Thanks
Holger
 
-----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

Bug or feature ? And how to deal with this ?

Thanks
Holger

.
Click to expand...

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!
 
Back
Top