Time-Defined IF Statement Does Not Update

[arnpry]

I have a formula that I've been trying to setup that reads a data value from the 4th row of the N Column IF the current time is less than 5:00am, ELSE pull from the 2nd row of the column.

<code>=IF(T2<"5",N4,N2)</code>

Formula for Current Time (Hour) in Cell T2:

<code>=TEXT(NOW(),"H")</code>

The purpose of this IF statement is to account for using old temperature data until 5am, when the new data comes in. I thought that I had this formula set up correctly but, for the past few mornings, it has not worked.

 

[Special-K99]

My first thought

=IF(HOUR(T2)<5,N4,N2)

cant see why yours isnt working though

 

[GlennUK]

If you want to do a numerical comparison on an hour, then don't use text.

=VALUE(TEXT(NOW(),"H"))
... and don't have the "5" in quotes.

Someone has already given a good alternative anyway.

 

[arnpry]

I removed the double quotes from around 5 to see if that will make it work or not... Will be checking back tomorrow morning before 5am to see if this fix worked or not...

 

[GlennUK]

I removed the double quotes from around 5 to see if that will make it work or not... Will be checking back tomorrow morning before 5am to see if this fix worked or not...

... as long as you also changed the formula.

 

[arnpry]

... as long as you also changed the formula.

Yes, I changed the formula from:

=IF(T2<"5",N4,N2)

to

=IF(T2<5,N4,N2)

 

[Jonmo1]

Your formula in T2 is returning a TEXT string...because of the function called....TEXT.

So your comparison of The TEXT value "5" in T2, to the NUMBER 5 is invalid.
You need to convert the TEXT string in T2 to a real number.

Try

=IF(T2+0<5,N4,N2)


Or change the formula in T2 as suggeted by Glenn in Post #3

 

[arnpry]

Of course! That makes sense now... The formula is trying to reference back to a number but, it's formatted as text...

Your formula in T2 is returning a TEXT string...because of the function called....TEXT.

So your comparison of The TEXT value "5" in T2, to the NUMBER 5 is invalid.
You need to convert the TEXT string in T2 to a real number.

Try

=IF(T2+0<5,N4,N2)


Or change the formula in T2 as suggeted by Glenn in Post #3

 

[arnpry]

Great success!!!!

Thanks again!