Microsoft KB Archive/101062

{| = Excel: ON.TIME Function May Run Macro at Midnight =
 * width="100%"|

Last reviewed: September 12, 1996

Article ID: Q101062 The information in this article applies to:
 * 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