Timesheet with multiple in/out breaks overtime

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.