Excel: ON.TIME() Function May Run Macro at Midnight

• Microsoft Excel for Windows, versions 4.0, 4.0a

SYMPTOMS

When you use the ON.TIME() function in Microsoft Excel, it may run a specified macro at midnight rather than at its assigned time if the assigned time is past midnight.

This behavior is not consistent.

CAUSE

The ON.TIME() function uses the GetCurrentDateTime routine, which gets the current time and then the current date from MS-DOS. If there is a roll-over from one day to the next between the two operations, the routine may pass incorrect information to the function, resulting in the macro running at midnight rather than at the specified time.

WORKAROUND

To work around this problem, have the ON.TIME() function refer to a macro that tests the current time. If the current time is the same as the specified time, the test macro should run the specified macro. If the current time is earlier than the specified time, the test macro should run the original ON.TIME() macro again.

Example

  A1: TimerMacro
  A2: =ON.TIME("6:00 AM","TestTime")
  A3: =RETURN()
  A4:
  A5: TestTime
  A6: =IF(TEXT(NOW(),"hh:mm AM/PM")="6:00 AM")
  A7: =GOTO(MyMacro)
  A8: =END.IF()
  A9: =GOTO(TimerMacro)
 A10: =RETURN()
 A11:
 A12: MyMacro
 A13: =ALERT("This is the correct time to run MyMacro")
 A14: =RETURN()

REFERENCES

"Function Reference", version 4.0, page 305