Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Dates & Cell Dates
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Dates & Cell Dates
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! "Graham Y" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Dates & Cell Dates
On Tue, 15 Jul 2008 04:30:01 -0700, Graham Y
wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Dates & Cell Dates
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.) "Ron Rosenfeld" wrote: On Tue, 15 Jul 2008 04:30:01 -0700, Graham Y wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Dates & Cell Dates
On Tue, 15 Jul 2008 06:53:02 -0700, Graham Y
wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linking computer dates (time) to spreadsheet dates that have formu | Excel Worksheet Functions | |||
how do I sort a column of random dates into Consecutive dates | Excel Worksheet Functions | |||
Toggle a range of Julian dates to Gregorian Dates and Back | Excel Programming | |||
Charting data against dates where dates are not at fixed intervals | Charts and Charting in Excel | |||
the dates on cell format make different dates. | New Users to Excel |