Time-Defined IF Statement Executed in Excel

Question

I have a formula that I've been trying to setup to read a data value from the 4th row of a column IF the current time is less than a certain value, ELSE pull from the 2nd row of the column.

=IF($Z$16<5,'SOME TEXT'!N4,'SOME TEXT'!N2)

Formula for Current Time (Hour) in Cell $Z$16:

=TEXT(NOW(),"H")

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 but, for the past few mornings, it has not worked.

Are you doing anything to recalculate the formula? They will only recalculate when something else in the sheet triggers this to happen (such as another formula being used) — Trum
– Trum, Commented Jun 24, 2015 at 10:55
Hi Trum. I'm not trying to recalculate anything actually. Just trying to display certain values depending on whether or not the current hour is less than 5am. — Aaron Perry
– Aaron Perry, Commented Jun 24, 2015 at 10:56
Hi Aaron, That's what I mean - the IF and the Hour won't change until there is a calculation - and so you'll end up with no change until afterwords. So you would have to press F9 to do this manually or use a Macro to do an intermittent update. — Trum
– Trum, Commented Jun 24, 2015 at 11:00
stackoverflow.com/questions/17924542/… This post might be very useful if you want to go down the macro route. — Trum
– Trum, Commented Jun 24, 2015 at 12:40

sancho.s ReinstateMonicaCellio · Accepted Answer · 2015-06-24 12:51:22Z

Use

=IF(<time-of-the-day><TIME(5,0,0),'SOME TEXT'!N4,'SOME TEXT'!N2)

You would replace <time-of-the-day> by any of

(NOW()-TODAY()) (NOW()-INT(NOW())) TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW())) TIMEVALUE(TEXT(NOW(),"H:M:S"))

There might be better options to isolate the current time of the day.

Collectives™ on Stack Overflow

Time-Defined IF Statement Executed in Excel

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related