Convert noon or midnight to clock times
Turn the word noon into 12:00 PM and midnight into 12:00 AM.
Download Excel templateMicrosoft 365
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | LOWER(TRIM(A8)) | Removes outer spaces and makes the word lowercase. | "noon" |
| 2 | "noon",TIME(12,0,0) | The word noon gives 12:00 PM. | 12:00 PM |
| 3 | "midnight",0 | The word midnight gives 0, shown as 12:00 AM. | Not used for row 8 |
| 4 | "Review wording" | Shown for any other words. | Not used for row 8 |
| 5 | SWITCH( … ) | Gives the answer that matches part 1. | 12:00 PM |
Read it left to right. Capitals and extra spaces don't matter.
=SWITCH(LOWER(TRIM(A8)),"noon",TIME(12,0,0),"midnight",0,"Review wording")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 times. Paste words like Noon or midnight into the green column of the Template tab. The clock times fill in by themselves.
- Move them into your letter. Copy the results. Then use Paste Special and choose Values.
Before you send
Midnight gives a time with no date, so it doesn't say which day. Add the date yourself if it matters.
Microsoft's help: SWITCH function
OTHER ROUTES YOU MIGHT LIKE

Read 24:00 as midnight the next day
November 4, 2026 + 24:00 → November 5, 2026 12:00 AM
Get the template →
Write a duration as days, hours, minutes
49:30 → 2d 1h 30m
Get the template →
Write a duration as minutes and seconds
49:30:15 → 2970m 15s
Get the template →
Show an appointment time as 1:05 PM
13:05:00 → 1:05 PM
Take this route →