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

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"
wrote in message
news:ChristopherM.26xciy_1146143700.5868@excelforu m-nospam.com...

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

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

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"ChristopherM"
wrote in message
news:ChristopherM.26xkuz_1146154502.0352@excelforu m-nospam.com...

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