VBA Dates & Cell Dates

[Graham Y]

I have a timesheet spreadsheet that happily calculates hours worked etc, for
a period. Names down the sheet, dates across the sheet.There are several
times and figures for each person, but at the end of the date range I have
some =SUM()'s to add all the hours up,that's fine. But I want to also add
these by department, as deparment size varies and I need to do some other
stuff with the data, I have a macro that works its way down the sheet, adding
up the figures I am interested in. However it doesn't work!
So I have adapted my code to display the figures and running totals
onanother sheet, and I have discovered that when I add 8 hours to 112 hours I
get 96 hours not 120 hours! The cells are formatted [hh]:mm as I am
interested in hours and not days. but when I look at the variable in my code
that holds the cummualtive total and the cell on the spreadsheet that should
have the same value they different!
Cell shows 04/01/1900 16:00:00 and the VBA 03/01/1900 16:00:00.
I've taken this back in 8 hours steps and 24 hours shows as 01/01/1900
00:00:00 in the cell and 31/12/1899 00:00:00 in my VBA. This oddity wouldn't
matter but suddenly when I get to 120 hours they both say the same and I end
up with 96:00 in my cell. There is a similar problem at the transition to 360
hours, (my data doesn't happen to have a transition at 240 hours).

This is obviously a BUG in excel, but any ideas how I should work around it?
I tried looking for the transition, but as the date in the cell isn't the
same as the value in the VBA that didn't work.

 

[Graham Y]

It occured to me this might be a bit like the bug in Excel 2007 where
850*77.1 displays the wrong amount, so insted of initialising all my 'total'
variables to 0, i start them at 1 second. Hey presto,, problem dissapears, my
totals are of course 1 second out!

 

[Ron Rosenfeld]

I have a timesheet spreadsheet that happily calculates hours worked etc, for
a period. Names down the sheet, dates across the sheet.There are several
times and figures for each person, but at the end of the date range I have
some =SUM()'s to add all the hours up,that's fine. But I want to also add
these by department, as deparment size varies and I need to do some other
stuff with the data, I have a macro that works its way down the sheet, adding
up the figures I am interested in. However it doesn't work!
So I have adapted my code to display the figures and running totals
onanother sheet, and I have discovered that when I add 8 hours to 112 hours I
get 96 hours not 120 hours! The cells are formatted [hh]:mm as I am
interested in hours and not days. but when I look at the variable in my code
that holds the cummualtive total and the cell on the spreadsheet that should
have the same value they different!
Cell shows 04/01/1900 16:00:00 and the VBA 03/01/1900 16:00:00.
I've taken this back in 8 hours steps and 24 hours shows as 01/01/1900
00:00:00 in the cell and 31/12/1899 00:00:00 in my VBA. This oddity wouldn't
matter but suddenly when I get to 120 hours they both say the same and I end
up with 96:00 in my cell. There is a similar problem at the transition to 360
hours, (my data doesn't happen to have a transition at 240 hours).

This is obviously a BUG in excel, but any ideas how I should work around it?
I tried looking for the transition, but as the date in the cell isn't the
same as the value in the VBA that didn't work.

Allegedly, that "bug" in Excel was deliberately placed there, back when it was
developed, to ensure compatibility with Lotus 1-2-3 (the leading spreadsheet
program at that time) which also had the same bug. The issue is that 1900 is
erroneously being considered a leap year. Microsoft apparently felt under no
constraints to maintain that bug within VBA.

One workaround would be to use a full or actual date/time in VBA (or in Excel).
(I assume you are not computing hours worked for Jan-Feb 1900).

Other workarounds come to mind, but without more information, it would be hard
to advise.
--ron

 

[Graham Y]

Thanks Ron
I think this is actually a rounding or conversion issue somewhere in the
process, as when I initialize the 'total' variables to 1 second and then
subtract 1 second at the end of the loop, my totals all match. ( I have a
'grand' total and three 'section' totals, which were not matching, hence
finding the bug.)

I have a timesheet spreadsheet that happily calculates hours worked etc, for
a period. Names down the sheet, dates across the sheet.There are several
times and figures for each person, but at the end of the date range I have
some =SUM()'s to add all the hours up,that's fine. But I want to also add
these by department, as deparment size varies and I need to do some other
stuff with the data, I have a macro that works its way down the sheet, adding
up the figures I am interested in. However it doesn't work!
So I have adapted my code to display the figures and running totals
onanother sheet, and I have discovered that when I add 8 hours to 112 hours I
get 96 hours not 120 hours! The cells are formatted [hh]:mm as I am
interested in hours and not days. but when I look at the variable in my code
that holds the cummualtive total and the cell on the spreadsheet that should
have the same value they different!
Cell shows 04/01/1900 16:00:00 and the VBA 03/01/1900 16:00:00.
I've taken this back in 8 hours steps and 24 hours shows as 01/01/1900
00:00:00 in the cell and 31/12/1899 00:00:00 in my VBA. This oddity wouldn't
matter but suddenly when I get to 120 hours they both say the same and I end
up with 96:00 in my cell. There is a similar problem at the transition to 360
hours, (my data doesn't happen to have a transition at 240 hours).

This is obviously a BUG in excel, but any ideas how I should work around it?
I tried looking for the transition, but as the date in the cell isn't the
same as the value in the VBA that didn't work.

Allegedly, that "bug" in Excel was deliberately placed there, back when it was
developed, to ensure compatibility with Lotus 1-2-3 (the leading spreadsheet
program at that time) which also had the same bug. The issue is that 1900 is
erroneously being considered a leap year. Microsoft apparently felt under no
constraints to maintain that bug within VBA.

One workaround would be to use a full or actual date/time in VBA (or in Excel).
(I assume you are not computing hours worked for Jan-Feb 1900).

Other workarounds come to mind, but without more information, it would be hard
to advise.
--ron

 

[Ron Rosenfeld]

Thanks Ron
I think this is actually a rounding or conversion issue somewhere in the
process, as when I initialize the 'total' variables to 1 second and then
subtract 1 second at the end of the loop, my totals all match. ( I have a
'grand' total and three 'section' totals, which were not matching, hence
finding the bug.)

I don't understand what you've written. But the one day difference between VBA
and Excel disappears on 1 Mar 1900. Since I don't really know how you are
attacking your problem, I'm afraid that's all I can give you. Hopefully you'll
be able to get things working to your satisfaction.

Best,
--ron