# 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?

Friday, December 16, 2016 7:07 PM

• 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)

• Marked as answer by Friday, December 16, 2016 9:00 PM
• Unmarked as answer by Friday, December 16, 2016 9:11 PM
• Marked as answer by Friday, December 16, 2016 9:22 PM
Friday, December 16, 2016 8:32 PM

### 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)

• Marked as answer by Friday, December 16, 2016 9:00 PM
• Unmarked as answer by Friday, December 16, 2016 9:11 PM
• Marked as answer by Friday, December 16, 2016 9:22 PM
Friday, December 16, 2016 8:32 PM
• 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 Friday, December 16, 2016 9:13 PM
Friday, December 16, 2016 9:01 PM
• How would you expect to compare A1 to the current time without using a volatile function? Do you only want the time to be checked when the workbook is opened, for example?

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Friday, December 16, 2016 9:32 PM
• Good point. No, it must be checked with the workbook open. Was just asking.

I´ll use your solution. Thanks Hans.

Friday, December 16, 2016 10:29 PM