Show PGY and program year separately

A fellow can be PGY-5 but in program year 2. Keep both on the roster.

What you'll make

In Excel: 5 + 2 becomes PGY-5; program year 2.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1OR(A8="",B8="")Checks if either year is empty.No
2"PGY-"&TEXT(A8,"0")Writes PGY- and the training year."PGY-5"
3&"; program year "&Adds the words between the two years."PGY-5; program …"
4TEXT(B8,"0")Adds the year in this program."PGY-5; … year 2"
5IF( … )Flags a missing year, else uses parts 2 to 4."PGY-5; … year 2"

Review missing GME year fields shows when part 1 says yes.

=IF(OR(A8="",B8=""),"Review missing GME year fields","PGY-"&TEXT(A8,"0")&"; program year "&TEXT(B8,"0"))

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 list. Paste each person's PGY and year in program into the green columns of the Template tab. The roster lines fill in by themselves.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

Use the years from your roster. Don't work them out from a start date or graduation year.

Microsoft's help: TEXT function