If function for time more or equal to 2 minutes

[kitevail]

Hi
I am a total newbie in Excel. I've been trying to find an answer to my problem on multiple forums without success. Can someone help me step by step resolve my issue.

I have in Column E(E1: E703) a list of time differences in the following format: 0:02:14 with hh:mm:ss
I managed to obtain these time differences by doing the following formula: =TEXT(B5-B4,"h:mm:ss")

In column F, I want to create a formula that labels all values more or equal to 1 minute from Column E and label those as "new animal".
I have tried the following formula for cell E11 with value 0:04:38 (clearly over 1 minute):
=IF(E11>TIME(0,1,0),1,"new animal") but it shows up as "1".

Can someone clearly explain to me what I am doing wrong?
Thanks a lot
Charlotte

[:) Sixthsense :)]

Use value in-front of E11 reference. Because you used =TEXT(B5-B4,"h:mm:ss") which will get only the text output instead of real time.

=IF(value(E11)>TIME(0,1,0),1,"new animal")

[kitevail]

Hi
Thanks for your help but it does not work. All results come up as "animal" even if it's less than 1 minute.
Can someone help me with this?
I've put the exact same formula but does not work.
Cheers
Charlotte

[Tony Valko]

Can someone clearly explain to me what I am doing wrong?

You're using this formula:

=TEXT(B5-B4,"h:mm:ss")

That is returning a TEXT string. Then you're comparing the text value against the numeric value of TIME(0,1,0). In Excel TEXT has a higher value than any number so:

E11>TIME(0,1,0) will ALWAYS be TRUE which is why your formula always returns 1.

Instead of:

=TEXT(B5-B4,"h:mm:ss")

All you need is:

=B5-B4

Then format as h:mm:ss

You should be able to use your original formula (unless the cell format of E11 is screwed up!):

=IF(E11>TIME(0,1,0),1,"new animal")