Answered by:
NOW with time condition formula
Question

Say we have this time (hh:mm) in A1:
13:32
And in A2 we want to show "ON" if NOW (PC time) is before the time in A1, and "OFF" if it´s after.
So if the current time is 13:31, A2 shows "ON"; then at 13:33 it shows "OFF".
Important 1: We don´t have/want the NOW() function alone in any cell. Trying to avoid volatile functions.
Important 2: This should work everyday.

This formula (in A2) won´t work:
=IF(NOW()<A1,"ON","OFF")
How can we do it?
Answers

NOW() includes the date as well as the time. Try this formula:
=IF(MOD(NOW(),1)<A1,"ON","OFF")
Regards, Hans Vogelaar (http://www.eileenslounge.com)
All replies

NOW() includes the date as well as the time. Try this formula:
=IF(MOD(NOW(),1)<A1,"ON","OFF")
Regards, Hans Vogelaar (http://www.eileenslounge.com)

It works. Thanks so much Hans.
Edit: I see that below the sheet is calculating as if the NOW function was alone in a cell.
Is there any way to do it without the NOW function?
We´re trying to avoid volatile functions.
 Edited by NicoPer Friday, December 16, 2016 9:13 PM

