Forum Discussion

oolliivviiaa's avatar
oolliivviiaa
Copper Contributor
Jan 26, 2022

Calculating Employee Work Hours, Minus Lunch/Break Time

Hi All, 

 

I need help creating a formula and what cell to put the formula in. (See my image of sheet below)

 

This is the formula I am using for the days of the week to calculate work hours:  =(ABS(B3-B4 )* 24)

 

I've tried =IF formula and it almost worked but ended up not being sufficient. 

 

If I need to redo the layout of the current sheet, I can.  Thanks for any advice and solutions!

 

Formulas and Functions

3 Replies

  • sophieturner's avatar
    sophieturner
    Brass Contributor
    May 16, 2026

    Hey! Great layout, this is very doable without restructuring your sheet.

    Your current formula =(ABS(B3-B4)*24) correctly calculates raw hours. You just need to extend it to check whether an "X" exists in the 30-min or 45-min lunch columns and deduct accordingly.

    Here's the updated formula:

    =(ABS(B3-B4)*24) - IF(M3="X",0.5,0) - IF(R3="X",0.75,0)

    Breaking it down:

    • ABS(B3-B4)*24 → your existing hours calculation (keep this as-is)
    • IF(M3="X",0.5,0) → if Monday has an X in the 30-min lunch column, deduct 0.5 hours
    • IF(R3="X",0.75,0) → if Monday has an X in the 45-min lunch column, deduct 0.75 hours

    Adjust column references per day:

    Day30 Min Lunch Col45 Min Lunch Col
    MondayMR
    TuesdayNS
    WednesdayOT
    ThursdayPU
    FridayQV

    (adjust these to match your actual sheet columns)

    To prevent both boxes being marked at the same time, use this safer version:

    =(ABS(B3-B4)*24) - IF(M3="X", 0.5, IF(R3="X", 0.75, 0))

    This checks 30-min first — if not marked, then checks 45-min. Only one deduction ever applies.

    For your weekly total row, SUM all daily result cells:

    =SUM(C3, E3, G3, I3, K3)

    (replace with your actual daily result cells)

    Important: Make sure your start/end time cells are formatted as Time in Excel (not text). If you see wrong results, select the time cells → Format Cells → Time → re-enter the times.

    If you ever want to quickly verify whether your formula result is correct, I've seen people use https://mauricettecalcul.fr alongside their Excel sheet, you enter start time, end time and break minutes and it instantly confirms the number. Handy for spot-checking your formulas.

    • oolliivviiaa's avatar
      oolliivviiaa
      Copper Contributor
      Jan 31, 2022

      Thanks for the reply. 

       

      I ended up creating a separate column for the lunch break. Then just subtracted the number from the work hours. 

       

      SergeiBaklan