I am trying to figure out why my formula isn't working. I am creating a
timesheet form and have correctly calculated the number of hours worked based
on a start and end time.

My problem is in needing to break out overtime. Currently, I am returning a
value of 12:30 total hours for an 8:30 am to 9 pm work day. In my next cell
down, I need to indicate how many of those hours are overtime (which is
anything over 10 hours, so the correct answer is 2:30). If there is no
overtime, I want to return a value of 0. (I would also be ok with returning
no value at all if there is no overtime.)

The formula that I am using is this:
=IF(B12=10,SUM(B12-10),"0")

The formula dialog is telling me that B12 (which has a value of 12:30) is
not greater than 10 (which is not true) and therefor returns a value of 0
since the calculation concludes that there is no overtime.

What am I doing wrong?

Total hours: SUM(TotalHoursInWeek) usually 40hours Mon-Fri
Regular hours: MIN(TotalHours,40/24) replace 40 if Reg.Hours are different
Overtime hours: TotalHours-RegularHours


"Sashacat7" wrote:

I am trying to figure out why my formula isn't working. I am creating a
timesheet form and have correctly calculated the number of hours worked based
on a start and end time.

My problem is in needing to break out overtime. Currently, I am returning a
value of 12:30 total hours for an 8:30 am to 9 pm work day. In my next cell
down, I need to indicate how many of those hours are overtime (which is
anything over 10 hours, so the correct answer is 2:30). If there is no
overtime, I want to return a value of 0. (I would also be ok with returning
no value at all if there is no overtime.)

The formula that I am using is this:
=IF(B12=10,SUM(B12-10),"0")

The formula dialog is telling me that B12 (which has a value of 12:30) is
not greater than 10 (which is not true) and therefor returns a value of 0
since the calculation concludes that there is no overtime.

What am I doing wrong?

Another option, since you're calculating overtime by day, not by week is:

A B
1 Start 8:30am
2 End 9:00pm
3 Total =B2-B1
4 Regular =IF(B3=TIME(10,0,0),TIME(10,0,0),B3)
5 Overtime = IF(B3=TIME(10,0,0),B3-TIME(10,0,0),0)

The problem is that Excel isn't intepreting your "10" as a time value--so I
think you need to convert it.

"Sashacat7" wrote:

I am trying to figure out why my formula isn't working. I am creating a
timesheet form and have correctly calculated the number of hours worked based
on a start and end time.

My problem is in needing to break out overtime. Currently, I am returning a
value of 12:30 total hours for an 8:30 am to 9 pm work day. In my next cell
down, I need to indicate how many of those hours are overtime (which is
anything over 10 hours, so the correct answer is 2:30). If there is no
overtime, I want to return a value of 0. (I would also be ok with returning
no value at all if there is no overtime.)

The formula that I am using is this:
=IF(B12=10,SUM(B12-10),"0")

The formula dialog is telling me that B12 (which has a value of 12:30) is
not greater than 10 (which is not true) and therefor returns a value of 0
since the calculation concludes that there is no overtime.

What am I doing wrong?

That did the trick!! Thank you!! I knew it had something to do with
converting time, just couldn't get my head around how to make the conversion.

(our people are contractors, so they get paid by day, not by week.)



"BekkiM" wrote:

Another option, since you're calculating overtime by day, not by week is:

A B
1 Start 8:30am
2 End 9:00pm
3 Total =B2-B1
4 Regular =IF(B3=TIME(10,0,0),TIME(10,0,0),B3)
5 Overtime = IF(B3=TIME(10,0,0),B3-TIME(10,0,0),0)

The problem is that Excel isn't intepreting your "10" as a time value--so I
think you need to convert it.

"Sashacat7" wrote:

I am trying to figure out why my formula isn't working. I am creating a
timesheet form and have correctly calculated the number of hours worked based
on a start and end time.

My problem is in needing to break out overtime. Currently, I am returning a
value of 12:30 total hours for an 8:30 am to 9 pm work day. In my next cell
down, I need to indicate how many of those hours are overtime (which is
anything over 10 hours, so the correct answer is 2:30). If there is no
overtime, I want to return a value of 0. (I would also be ok with returning
no value at all if there is no overtime.)

The formula that I am using is this:
=IF(B12=10,SUM(B12-10),"0")

The formula dialog is telling me that B12 (which has a value of 12:30) is
not greater than 10 (which is not true) and therefor returns a value of 0
since the calculation concludes that there is no overtime.

What am I doing wrong?