Note rejected rows with accepted hours
Say when a staffing file still has rejected rows, so a partial total isn't taken as complete.
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | B8>0 | Checks if any rows were rejected. | TRUE |
| 2 | TEXT(A8,"0.00")&" … rejected row(s)" | Hours with two decimals, then the rejected count. | "124.50 accepted …" |
| 3 | TEXT(A8,"0.00")&" … supplied" | Hours with two decimals and no rejected rows. | Not used for row 8 |
| 4 | IF( … ) | Shows part 2 if part 1 is TRUE, else part 3. | "124.50 accepted …" |
Read it left to right. The caption keeps the hours as accepted only; it doesn't add back rejected rows.
=IF(B8>0,TEXT(A8,"0.00")&" accepted hour(s); review "&B8&" rejected row(s)",TEXT(A8,"0.00")&" accepted hour(s); no rejected rows supplied")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 totals. Paste the accepted hours and the rejected row count into the green columns of the Template tab. The captions fill in by themselves.
- Move them into your letter. Copy the results. Then use Paste Special and choose Values.
Before you send
Take both numbers from the same final validation report. A count from another file makes the caption wrong.
Microsoft's help: TEXT function
OTHER ROUTES YOU MIGHT LIKE

Subtract the larger meal time from hours
8.5 hours, 0.25 meal, 30-minute minimum → 8 hours
Get the template →
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 →
Merge a few test letters before the whole mailing
Whole mailing → records 2 to 4 only
Take this route →