1

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.

pnuts
  • 58,317
  • 11
  • 87
  • 139
Aaron Perry
  • 1,021
  • 1
  • 13
  • 33
  • 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 Jun 24 '15 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 Jun 24 '15 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 Jun 24 '15 at 11:00
  • http://stackoverflow.com/questions/17924542/excel-recalculating-every-x-seconds This post might be very useful if you want to go down the macro route. – Trum Jun 24 '15 at 12:40

1 Answers1

0

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.

sancho.s ReinstateMonicaCellio
  • 14,708
  • 20
  • 93
  • 185