Timesheet with multiple in/out breaks overtime

Copper Contributor

Timesheet with multiple in/out breaks overtime

I am struggling to make the right overtime formula.

it would be much appreciated if you could help.

cell/a               cell/b                    cell/c         cell/d         cell/e       cell/f          cell/g        cell/h

date        =TEXT(A1,"dddd")      09:05am   16:35pm    19:00pm   12:00am     empty       empty

Regular hours formula at cell/I is ;

=IF(WEEKDAY(A1,2)>=6,"",IF(WEEKDAY(A1,2)=5,6.5,7))

Overtime hours formula at cell/j is ;

=IF(IF(IF(((D1-C1)*24-0.5)>I1,(D1-C1)*24-0.5-I1,0)/24=0,"",IF(((D1-C1)*24-0.5)>I1,(D1-C1)*24-0.5-I1,0)/24)+(F1-E1+24)+(H1-G1+24)<=0,"",IF(IF(((D1-C1)*24-0.5)>I1,(D1-C1)*24-0.5-I1,0)/24=0,"",IF(((D1-C1)*24-0.5)>I1,(D1-C1)*24-0.5-I1,0)/24)+(F1-E1+24)+(H1-G1+24))

The issue is if I put 16:40pm in cell/d  overtime hours formula works fine. However, if I put 16:35pm in cell/d  overtime hours cell shows #VALUE! instead of hours.

How do I fix this?

Re: Timesheet with multiple in/out breaks overtime

It appears that the issue may be related to how Excel handles time values in the IF formula. Instead of directly comparing the time values, you can use the TIME function to create time values for comparison. Here's an adjusted version of your overtime hours formula at cell J:

``````=IF(
IF(
IF(
(
(
(D1-C1)*24-0.5
)>I1,
(
(D1-C1)*24-0.5-I1
),
0
)/24=0,
"",
(
(
(D1-C1)*24-0.5-I1
),
0
)/24
)+(F1-E1+24)+(H1-G1+24)<=0,
"",
IF(
IF(
(
(D1-C1)*24-0.5
)>I1,
(
(D1-C1)*24-0.5-I1
),
0
)/24=0,
"",
(
(
(D1-C1)*24-0.5-I1
),
0
)/24
)+(F1-E1+24)+(H1-G1+24)
)
)``````

This formula checks if the time difference (D1-C1)*24-0.5 is greater than I1 and adjusts the logic accordingly. The key change is using the TIME function to create a time value for the comparison.

Additionally, I noticed some redundancies in your formula, and I've attempted to simplify it while maintaining the intended logic. Please test this formula and adjust it as needed based on your specific requirements and data structure.The text was revised with the AI.

My answers are voluntary and without guarantee!