Showing 12 o'clock as 'noon' or 'midnight' in Excel
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’ or any other time label you’d like.
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.
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.
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))
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)
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