# Excel 2010 input blank for all 0 results

I use this

=IF(MINUTE(E59)+(SECOND(E59))=0,"",(HOUR(E59)*60)+(MINUTE(E59)+IF(SECOND(E59)<30,0,1)))

To calculate time into minutes like 00:06:13 = 6 as a result and 00:00:00 = blank as a result.

However when I use it against 00:00:19. the result is 0 and I would like it to be blank if less then 30 seconds.

• To clarify, you want a blank cell for all time values < 30 seconds? – Indrek Sep 5 '12 at 13:28

## 3 Answers

You probably want to change your condition:

`````` =IF(AND(HOUR(E59)=0,MINUTE(E59)=0,SECOND(E59))<30,"",(HOUR(E59)*60)+(MINUTE(E59)+IF(SECOND(E59)<30,0,1)))
``````
• using your formula it displays 0 as a result – Tom Sep 5 '12 at 18:07

As per `m4573r` answer, am not sure AND function can be written in excel as

``````condition1 AND condition2 AND condition3
``````

As far as I know, AND function can be written as follows:-

``````=IF(AND(HOUR(A1)=0, MINUTE(A1)=0, SECOND(A1)<30), "", (HOUR(A1)*60)+(MINUTE(A1)+IF(SECOND(A1)<30,0,1)))
``````
• You're correct, I updated my answer. – m4573r Sep 5 '12 at 14:11
• I tried your formula and it still displays 0 as a result – Tom Sep 5 '12 at 18:08

You don't really need all those SECOND/MINUTE/HOUR functions, just use a simple IF function to show blank if E59 < 30 seconds and if not you can multiply by 1440 to give the minutes, and round to get those to the nearest minute, i.e.

`=IF(E59<"0:00:30"+0,"",ROUND(E59*1440,0))`