Prevent a repeated meal deduction

Take a manual meal off reported hours only if it isn't already taken off.

What you'll make

In Excel: 8.5 hours, 0.5 meal, deduction requested becomes 8 hours.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1OR(NOT(ISLOGICAL(C8)), … )Checks that C and D both hold TRUE or FALSE.FALSE
2AND(C8,D8)Checks if the meal is already off and asked for again.FALSE
3IF(B8>A8, … ,A8-B8)Takes the meal off, or flags a meal too long.8
4IF(D8, … ,A8)Uses part 3 when asked for, else keeps the hours.8
5IF( … )Flags part 1 or 2, else shows part 4.8

Works from the inside out. Columns C and D must hold TRUE or FALSE, not yes or no.

=IF(OR(NOT(ISLOGICAL(C8)),NOT(ISLOGICAL(D8))),"Review meal deduction flags",IF(AND(C8,D8),"Review duplicate meal deduction",IF(D8,IF(B8>A8,"Review meal exceeds source hours",A8-B8),A8)))

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 the reported hours, meal hours, and TRUE or FALSE for each flag into the Template tab's green columns. The net hours fill in by themselves.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

Mark TRUE in Already deducted when the hours already have the meal taken off. The formula doesn't decide whether a meal applies.

Microsoft's help: IS functions