Share via

Excel formula to take time off from a time range

Toby Stapleton-Cotton 20 Reputation points
2025-11-01T22:23:21.7966667+00:00

I am trying to get Cell D2 to give me a result based on the result in C2. I have 4 different time "zones" Each time slot has an amount of time taken off so if the employee starts at 7am and finishes at 11am they would be in the system for 4 hours. On 4 hours they get a 15 minute break and so will only be paid for 3 hours and 45 minutes. the formula works fine with one instance but as soon as I string the rest it goes hay wire. Any help / advise would be greatly appreciated. The formula I am trying to run is as follows: =IF(AND(C2>=TIME(4,0,0),C2<=TIME(5,59,0))*C2-TIME(0,15,0),AND(C2>=TIME(6,0,0),C2<=TIME(7,59,0))*C2-TIME(0,30,0)*AND(C2>=TIME(8,0,0),C2<=TIME(8,59,0))*C2-TIME(0,60,0)*AND(C2>=TIME(9,0,0),C2<=TIME(11,59,0))*C2-TIME(0,90,0))

Microsoft 365 and Office | Excel | Other | MacOS
0 comments
Answer accepted by question author
  1. Kimberly Olaño 19,780 Reputation points Independent Advisor
    2025-11-02T01:16:55.7233333+00:00

    Thanks for sharing the details, Toby! Your current formula goes wrong because you’re using * instead of nested IFs or correct logical structures. The syntax is not valid Excel logic order.

    Try this instead:

    =IF(AND(C2>=TIME(4,0,0),C2<=TIME(5,59,0)),C2-TIME(0,15,0),

    IF(AND(C2>=TIME(6,0,0),C2<=TIME(7,59,0)),C2-TIME(0,30,0),

    IF(AND(C2>=TIME(8,0,0),C2<=TIME(8,59,0)),C2-TIME(1,0,0),

    IF(AND(C2>=TIME(9,0,0),C2<=TIME(11,59,0)),C2-TIME(1,30,0),

    C2))))

    You “nest” the IFs to test multiple time ranges. Each condition checks the total worked time (C2).

    See if this helps. If you need further assistance just let me know.

    Best regards,

    Kimberly

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.