Convert an hour to 12-hour numbering
Turn the time 2:30:15 PM into the hour 2. Midnight and noon both give 12.
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | HOUR(A8) | The hour of the time, from 0 to 23. | 14 |
| 2 | -1 | Takes away 1. | 13 |
| 3 | MOD( … ,12) | Keeps what is left after counting in twelves. | 1 |
| 4 | +1 | Adds 1 back, so the hours run 1 to 12. | 2 |
MOD wraps parts 1 and 2, so Excel works from the inside out.
=MOD(HOUR(A8)-1,12)+1How 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 times. Paste real times into the green column of the Template tab. The hours fill in by themselves.
- Move them into your letter. Copy the results. Then use Paste Special and choose Values.
Before you send
This gives the hour number only, so 2 AM and 2 PM both give 2. Add AM or PM if you need it.
Microsoft's help: HOUR function
OTHER ROUTES YOU MIGHT LIKE

Build a clock time from 12-hour fields
2 + 30 + p.m. → 2:30 PM
Get the template →
Build an end timestamp across midnight
11/4/2026 2:30:15 PM + 9:05:00 AM → 11/5/2026 9:05:00 AM
Get the template →
Join date and clock text into a timestamp
4-Nov-2026 + 2:30:15 PM → November 4, 2026 2:30:15 PM
Get the template →
Show an appointment time as 1:05 PM
13:05:00 → 1:05 PM
Take this route →