Written by Allen Wyatt (last updated September 16, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365
Lorene has a column that contains various times of day. She needs to round the times to the nearest half hour and display them as, for example, 10 am and 10:30 am. (Lowercase, minutes shown for only the half hour, and lowercase am/pm indicator.) She wonders if this is something she can do with a custom format, or if she needs to use a formula.
The short answer is that you cannot get the desired result with just a custom format. Instead, you'll need to use three features of Excel: a formula, a custom format, and a conditional format, all working in conjunction with each other. How you can round times has been covered in a different ExcelTip, here:
https://tips.net/T11401
To boil it down succinctly, in Lorene's case you could round the time to the nearest half hour in the following manner:
=MROUND(A1,"0:30")
This assumes, of course, that the original time is in cell A1. You can then apply the following custom format to the result of the formula:
h:mm a/p\m
This will display the results of the formula as hours and minutes with an am/pm indicator. This, however, still isn't quite what Lorene is looking for, which is no minutes indicated if the minutes are ":00". The final step can be accomplished by adding a conditional format to the cell. Assuming that your formula is in cell B1, follow these steps:
Figure 1. The New Formatting Rule dialog box.
=MINUTE(B1)=0
h a/p\m
That's it; the cell will now display the formatted time value as Lorene desired.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13913) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
When adding values to a time to calculate a new time, you may naturally choose to use the TIME function. This can cause ...
Discover MoreWhen working with elapsed times, you may want to calculate an average of those times. This tip demonstrates just how easy ...
Discover MoreNeed to check the current time in a formula you are putting together? It can sometimes be tricky to remember what Excel ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments