Write a service date or service period
Turn a start and end date into 11/04/2026 through 12/04/2026, or write one service date.
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | A8<>"" | Checks for a single service date. | No |
| 2 | OR(B8<>"",C8<>""),"Review both…" | Flags a row with both a date and a period. | Not used for row 8 |
| 3 | TEXT(A8,"mm/dd/yyyy") | Writes the single date. | Not used for row 8 |
| 4 | TEXT(B8,…)&" through "&TEXT(C8,…) | Writes the start through the end. | "11/04/2026 through…" |
| 5 | IF( … ) | Picks the date, the period or a message. | "11/04/2026 through…" |
Read it left to right. Before part 4, two checks catch a row with no dates or half a period.
=IF(A8<>"",IF(OR(B8<>"",C8<>""),"Review both date and period",TEXT(A8,"mm/dd/yyyy")),IF(AND(B8="",C8=""),"Service context not supplied",IF(OR(B8="",C8=""),"Review partial service period",TEXT(B8,"mm/dd/yyyy")&" through "&TEXT(C8,"mm/dd/yyyy"))))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 dates. Paste one service date, or a period start and end, into the green columns of the Template tab.
- Move them into your letter. Copy the results. Then use Paste Special and choose Values.
Before you send
Use the dates the source gives for the service. Don't fill a gap with the date the response was made.
Microsoft's help: TEXT function
OTHER ROUTES YOU MIGHT LIKE

Write a benefit caption with full scope
Labs + In + Family + Annual → Labs (In; Family; Annual)
Get the template →
Count visits left without hiding overuse
20 allowed, 6 used → 14 left
Get the template →
Find benefit money left in one currency
500.00 USD allowed, 125.50 USD used → 374.50 left
Get the template →
Merge a few test letters before the whole mailing
Whole mailing → records 2 to 4 only
Take this route →