Subtract the larger meal time from hours

Take out actual meals or the per-shift minimum, whichever is more.

What you'll make

In Excel: 8.5 hours, 0.25 meal, 30-minute minimum becomes 8 hours.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1MAX(B8,C8*D8/60)The larger of actual meals and the minimum.0.5
2IF(d>A8,"Review …",A8-d)Takes part 1 off the gross hours, or flags it.8
3LET( … )Names part 1's answer d so part 2 can use it.8

The minimum is full shifts times minutes per shift, divided by 60 for hours.

=LET(d,MAX(B8,C8*D8/60),IF(d>A8,"Review meal exclusion exceeds gross hours",A8-d))

How to use it

Yellow bar at the top? Click Enable Editing.

  1. See it work. The Example tab shows 20 made-up examples with the formula already in.
  2. Use your own hours. Paste gross hours, actual meal hours, full shifts and the minimum minutes into the green columns of the Template tab. The hours after meals fill in by themselves.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

Use gross hours with no meal time taken out yet, or meals come off twice. This is for staffing reports, not pay.

Microsoft's help: MAX function