Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

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

3 Answers 3

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))) 
share|improve this answer
    
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)))
share|improve this answer
    
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))

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.