Results 1 to 7 of 7

20070925, 22:01 #1
 Join Date
 Aug 2001
 Location
 Surrey, United Kingdom
 Posts
 1,001
 Thanks
 0
 Thanked 0 Times in 0 Posts
Negative time Calculations (2003 SP2)
Good evening
I am trying to make a basic 'timesheet' workbook for my drivers but I am having trouble calculating with times, I have managed to get some calculations working whereby people may start before midnight and finish after but I am have trouble in showing whether the driver worked overtime hours or did not work enough hours in a week.
If you look at the attched workbook you will see that I have tried to calculate the expected hours worked against the actual hours worked, if for instance it is 5 hours more a simple 5 or 5.00 would suffice and on the other hand if they worked 5 hours less than their shift I would like it to show 5 or 5.00, I have tried all of the time, custom, number and general options but cannot seem to achieve what I want.
Cheers
SteveCheers
Steve
Asking the questions everbody wants the answers too but feels too stupid to ask themselves :)

20070925, 22:38 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Negative time Calculations (2003 SP2)
E10"D10" doesn't make sense  you're trying to subtract the text "D10" from the time value in E10. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
You can use the simple formula
=24*(D10E10)
in E11, and set the number format of this cell to Number with 2 decimal places. If somebody works 2 hours and 30 minutes overtime, you'll see 2.50, and if somebody works 1 hour and 20 minutes too short, you'll see 1.33.
If you need to see the time in the format h:mm, use the formula
=ABS(D10E10)
and format as time. You'll have to use another cell to indicate whether this is + or , for example by using the formula
=IF(D10<0,"","+")

20070925, 23:02 #3
 Join Date
 Aug 2001
 Location
 Surrey, United Kingdom
 Posts
 1,001
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Negative time Calculations (2003 SP2)
Thanks Hans
I thought I had changed the cell back to time, thanks for you examples I am off to play with them now, I did (amazingly) find a work around similar to your first suggestion, I made another column and entered =(D5E5)*24 and then totalled it in F10 but what took you seconds took me hours!! I like the idea of the time format though, thanks as usual
Cheers
Steve
SteveCheers
Steve
Asking the questions everbody wants the answers too but feels too stupid to ask themselves :)

20070926, 12:47 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Negative time Calculations (2003 SP2)
Another option than those suggested that worked in older versions of XL (I don;t have XL2003) is to set under toolsoptions to the 1904 date system. This will allow displaying directly the negative times...
Steve

20070926, 19:02 #5
 Join Date
 Aug 2001
 Location
 Surrey, United Kingdom
 Posts
 1,001
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Negative time Calculations (2003 SP2)
Thanks Steve
I actually ran across an article some time ago about this and it said something about using extreme caution when using it because in caused some time anomalies, I cannot remember if it was 4 days 1 hour (doesn't MS Excel think that 1900 was a leap year? perhaps thats it) or 1 day 4 hours, but I will take another look
Thanks for the suggestion
SteveCheers
Steve
Asking the questions everbody wants the answers too but feels too stupid to ask themselves :)

20070926, 19:18 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Negative time Calculations (2003 SP2)
Existing dates will change (by 4 years and 1 day) if you turn on the 1904 date system, but only in the active workbook. The 1904 setting is stored in the workbook, it won't affect other workbooks.

20070926, 22:31 #7
 Join Date
 Aug 2001
 Location
 Surrey, United Kingdom
 Posts
 1,001
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Negative time Calculations (2003 SP2)
Thanks for the info Hans
SteveCheers
Steve
Asking the questions everbody wants the answers too but feels too stupid to ask themselves :)