Showing 12 PM as 'noon' in Excel

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

Excel shows midday or noon as 12:00 PM which doesn’t suit everyone, here’s how to change the defaults to a word such as ‘Midnight’.

ABC News in Australia delves into the argument about showing  12 PM – There is no such time as 12pm. The argument is that 12:00 is neither ‘AM’ (ante or before meridian) nor ‘PM’ (post meridian).  Strictly speaking that’s correct but most people accept 12 PM meaning noon or midday.

That’s how Excel works.  Noon is 12 PM and Midnight is 12 AM.

12 PM or noon in Excel - Showing 12 PM as 'noon' in Excel

We’re not bothered by 12 PM or 12 AM too much but we’d prefer clearer labels like ‘noon’, ‘midday’ or ‘midnight’.

The name labels are less likely to be misunderstood.

Adding time names to Excel

To change what’s displayed in time cells you need a formula in another cell. Changing times to names needs nested IF statements or the new IFS() in Excel 365.

12 PM or noon in Excel 1 1 - Showing 12 PM as 'noon' in Excel

It’s not possible to use cell formatting nor conditional formatting.  The latter can change the formatting of a cell but not the value in that cell.

Test the value against a specific time (using the Time() function).

=IF(A2=TIME(12,0,0),”Noon”,IF(A2=TIME(0,0,0),”Midnight”,A2))

12 PM or noon in Excel 1 2 - Showing 12 PM as 'noon' in Excel

IFS is a better way to handle multiple conditions, if you have the latest Excel.

=IFS(A2=TIME(12,0,0),”Noon”,A2=TIME(0,0,0),”Midnight”,TRUE,A2)

12 PM or noon in Excel 1 4 - Showing 12 PM as 'noon' in Excel

You can add other special times:

…=TIME(10,0,0),”Tea time”

…=TIME(15,0,0),”Afternoon Tea”

…=TIME(17,0,0),”Quitting Time”

IF and Nested IF Statements in Excel

Excel worksheet protection is not encryption

Moving Average in Excel Charts

 

subs profile e1563205311409 - Showing 12 PM as 'noon' in Excel
Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address