How to Format Excel Times to Hide Zero Minutes
Question details
The user needs to format time values in Excel to display without minutes if the minutes are exactly zero (e.g., displaying 10:00 AM as 10 AM), while keeping the minutes visible if they are greater than zero (e.g., 10:30 AM).
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating a clean and readable schedule or timesheet where redundant zero minutes are visually omitted to save space.
- Observed behavior
- Standard Excel custom number formats do not support conditionally hiding the minute component based on its value, causing it to uniformly display 10:00 AM instead of 10 AM.
Ensure your time data is entered as valid Excel time values (and not plain text) so that formulas can correctly evaluate the minutes.
Use Conditional Formatting to Hide Zero Minutes
Apply a conditional formatting rule with a formula to conditionally change the number format only when the minutes equal zero.
Because a standard custom number format cannot conditionally hide minutes based on the time value itself, you must combine standard formatting with conditional formatting. This approach applies a base format for times with minutes, and a special rule for times exactly on the hour.
Select the entire range containing your time data. Right-click, choose 'Format Cells', go to the 'Custom' category, and apply the base format 'h:mm AM/PM'.
With the time range still selected, go to the 'Home' tab on the Excel ribbon, click 'Conditional Formatting', and select 'New Rule'.
Choose 'Use a formula to determine which cells to format'. In the formula box, enter '=MINUTE(A2)=0'. Replace 'A2' with the reference of the active (first) cell in your selected range.
Click the 'Format' button, navigate to the 'Number' tab, and select 'Custom'. In the Type box, enter 'h AM/PM' to hide the minutes. Click OK twice to apply the rule.
Easily Format Times and Data in WPS Spreadsheet
WPS Office Spreadsheet provides a highly compatible and intuitive interface for handling complex conditional formatting and custom number formatting, helping you create professional schedules with ease.
- 1. Format base cells: Open your spreadsheet in WPS Office, select your time range, and press Ctrl+1 to set the base format to 'h:mm AM/PM'.
- 2. Add conditional formatting: Navigate to the Home tab, click on 'Conditional Formatting', and select 'New Rule'.
- 3. Apply formula and custom format: Select 'Use a formula to format cells', type '=MINUTE(A2)=0', click Format to set the custom type to 'h AM/PM', and click OK.
Frequently Asked Questions
Why can't I use a standard custom number format to hide zero minutes?
Excel's standard custom number format rules only support conditional logic for positive, negative, zero, and text values. They cannot evaluate specific components of a time value, such as checking if the minutes equal zero. Therefore, conditional formatting is required.
How do I hide zero minutes when using 24-hour (military) time?
Set the base cell format for your selected range to 'h:mm'. Then, when creating your conditional formatting rule using the '=MINUTE(A2)=0' formula, set the custom format to simply 'h'.
Will hiding the zero minutes affect my time calculation formulas?
No. Conditional formatting only alters the visual display of the data. The underlying cell value remains a complete time serial number, so all mathematical calculations involving those times will remain perfectly accurate.
