Subtract the larger meal time from hours
Take out actual meals or the per-shift minimum, whichever is more.
Download Excel templateMicrosoft 365
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | MAX(B8,C8*D8/60) | The larger of actual meals and the minimum. | 0.5 |
| 2 | IF(d>A8,"Review …",A8-d) | Takes part 1 off the gross hours, or flags it. | 8 |
| 3 | LET( … ) | 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.
- See it work. The Example tab shows 20 made-up examples with the formula already in.
- 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.
- 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
OTHER ROUTES YOU MIGHT LIKE

Count nursing hours only when onsite
8.5 hours, nursing, offsite → 0 hours
Get the template →
Get decimal hours by six-minute bands
7 hours 33 minutes, six-minute bands → 7.6 hours
Get the template →
Note rejected rows with accepted hours
124.5, 3 rejected → 124.50 accepted hour(s); review 3 rejected row(s)
Get the template →
Merge a few test letters before the whole mailing
Whole mailing → records 2 to 4 only
Take this route →