Adding and Calculating time on a timesheet

[Niven]

Hi,

I have a timesheet where the user enters start and end time for each day. The calculated cell below gives the difference between start and end time i.e. number of hours worked.

I want to split the hours worked into Normal Hours and Overtime Hours.

For Normal Hours I am using =IF(SUM(E28:I28)>40,"40","SUM(E28:I28)).
For Overtime Hours I am using =IF(SUM(E28:I28)>40,"SUM(E28:I28)-40","0")

The first calculation just gives the total hours, not 40 and the second calculation gives 0.

I seem to think it has to do with the format. Can I use another function or should I use vba code? What code will work?

Thanks.

 

[anthonya2369]

I would create another cell that references the normal hours with formatting of 40:00:00 and then use that cell for the calculation. So if I put the "40:00:00" in G1, the formula would be this (assuming the time format '37:30:55' was used):

=IF(SUM(E28:I28)>G1,G1,SUM(E28:I28))

=IF(SUM(E28:I28)>G1,SUM(E28:I28)-G1,0)

 

[Niven]

Thanks Anthony, that was really easy and it worked!