G
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.
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.