2

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
4

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
3

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
2

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))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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