I work on a number of spreadsheets that do many complex calculations on times (minutes and seconds), and I want to hide the values where the result is zero. However, sometimes the results are not exactly zero, so even with the sheet set to hide zero values, a zero might still show.
I know that one way to fix this is to surround the formula with =Round(t*86400,0)/86400 where 86400 is the number of seconds in a day and t is the formula returning a time result. But for a spreadsheet that already has many intensive calculations (including some custom ones), that’s a lot of extra work (for both me and the recalculation process).
Is there an easier way to hide times that are zero minutes and zero seconds, but may be returning a result that is, for example 3.46945E-18 (which is 3 ten-millionths of a second)?
4
Apply formatting like this:
- Format cells … -> Number -> Custom
type in the Type box
[<0,0000115]"",hh:mm:ss
this will conditional formatting the cell to empty string or hh:mm:ss format.
1