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

In Excel: 124.5, 3 rejected becomes 124.50 accepted hour(s); review 3 rejected row(s).

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1B8>0Checks if any rows were rejected.TRUE
2TEXT(A8,"0.00")&" … rejected row(s)"Hours with two decimals, then the rejected count."124.50 accepted …"
3TEXT(A8,"0.00")&" … supplied"Hours with two decimals and no rejected rows.Not used for row 8
4IF( … )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.

  1. See it work. The Example tab shows 20 made-up examples with the formula already in.
  2. 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.
  3. 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