none
NOW with time condition formula RRS feed

  • 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

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)

    • Marked as answer by NicoPer Friday, December 16, 2016 9:00 PM
    • Unmarked as answer by NicoPer Friday, December 16, 2016 9:11 PM
    • Marked as answer by NicoPer 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 NicoPer Friday, December 16, 2016 9:00 PM
    • Unmarked as answer by NicoPer Friday, December 16, 2016 9:11 PM
    • Marked as answer by NicoPer 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 NicoPer 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