Convert noon or midnight to clock times

Turn the word noon into 12:00 PM and midnight into 12:00 AM.

What you'll make

In Excel: Noon becomes 12:00 PM; midnight becomes 12:00 AM.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1LOWER(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",0The 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
5SWITCH( … )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.

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