Add/Subtract hours

[ChristopherM]

I have a timesheet which records for each day of the week the time an employee started and finished, and by subtracting one from the other I can get the number of hours worked each day. I can also add these hours to give a total for the week which is formatted with "[hh]" to show a figure such as 43:45. However, I then want to subtract from that figure the employee's standard hours (say 35) to show the overtime (either in hh:mm or decimal - I don't mind). However every formula I have tried just gives "#####" and when you pass the cursor over it a tip saying "negative dates or times are displayed as #####". The result of the calculation isn't (shouldn't be) negative however. Can anyone suggest a formula to help? Thanks in advance for your help.

[Bob Phillips]

Subtract TIME(35,0,0).

Also, cater for less than 35 hours worked with

=MAX(0,total_time-TIME(35,0,0))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"ChristopherM" <[email protected]>
wrote in message
news:[email protected]...
>
> I have a timesheet which records for each day of the week the time an
> employee started and finished, and by subtracting one from the other I
> can get the number of hours worked each day. I can also add these
> hours to give a total for the week which is formatted with "[hh]" to
> show a figure such as 43:45. However, I then want to subtract from
> that figure the employee's standard hours (say 35) to show the overtime
> (either in hh:mm or decimal - I don't mind). However every formula I
> have tried just gives "#####" and when you pass the cursor over it a
> tip saying "negative dates or times are displayed as #####". The
> result of the calculation isn't (shouldn't be) negative however. Can
> anyone suggest a formula to help? Thanks in advance for your help.
>
>
> --
> ChristopherM
> ------------------------------------------------------------------------
> ChristopherM's Profile:
http://www.excelforum.com/member.php...o&userid=23564
> View this thread: http://www.excelforum.com/showthread...hreadid=536777
>

[ChristopherM]

Thanks for your help - the only problem is that the hours has to be less than 24 for it to work. If I enter 35, it treats the value as 5!

[Bob Phillips]

You need to format the result cell as [h]:mm

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"ChristopherM" <[email protected]>
wrote in message
news:[email protected]...
>
> Thanks for your help - the only problem is that the hours has to be less
> than 24 for it to work. If I enter 35, it treats the value as 5!
>
>
> --
> ChristopherM
> ------------------------------------------------------------------------
> ChristopherM's Profile:
http://www.excelforum.com/member.php...o&userid=23564
> View this thread: http://www.excelforum.com/showthread...hreadid=536777
>