Excel: How to hide hours or minutes if either is zero? [closed]

Question

Right now I have formatted my cell with:

h "hours" m "minutes"

So if my cell has 7:00, it displays as 7 hours 0 minutes. Is there a way to remove the hours or the minutes if these are zero? Something like 7 hours, or 0:30 as 30 minutes

Answer 1

If you have Excel 2007 or later versions (but apparently not Excel for Mac 2011) you can achieve this with a combination of regular formatting and conditional formatting.

Assuming data in A1 down use a regular custom format like this

[<0.0415][m]" minutes";h "hours" m "minutes"

That will give you the same as your previous formatting except that any value < 01:00 will display like

30 minutes

Now you can add a conditional formatting condition. With column A selected do this

Conditional Formatting > New Rule > Use a formula to determine which cells to format > type this formula in the box

=MINUTE(A1)=0

Click the "format" button and set the following number format

h" hours"

Now if you have 16:00 in A1 that will display as just 16 hours

[conditional formatting rules will always supercede regular formatting]

If you are dealing with "elapsed" times rather than clock times then you might want to use [h] rather than h in all the hours formats to show hours 24+