# Thread: Negative time Calculations (2003 SP2)

1. ## 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

Steve

2. ## 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*(D10-E10)

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(D10-E10)

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,"-","+")

3. ## 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 =(D5-E5)*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
Steve

4. ## 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 tools-options to the 1904 date system. This will allow displaying directly the negative times...

Steve

5. ## 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

Steve

6. ## 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.

7. ## Re: Negative time Calculations (2003 SP2)

Thanks for the info Hans

Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•