Forum Discussion

Fluent1111's avatar
Fluent1111
Copper Contributor
Nov 15, 2023

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?

 

Formulas and Functions
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Nov 15, 2023

    Fluent1111 

    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!

     

    Hope this will help you.

     

    Was the answer useful? Mark as best response and like it!

    This will help all forum participants.

Resources