Share via

How to use MOD in Excel VBA to calculate time?

Anonymous
2023-02-09T01:01:00+00:00

I have an application that needs to calculate the time to launch an OnTime event. Sometimes the current time will before 12:00 AM and the calculated time will be a few minutes after 12:00 AM.

In Excel I can use the formula "=MOD(Now() + TimeValue("00:10:00"),1) and get the desired result.

In VBA, the formula XX = Now + TimeValue("00:10:00") MOD 1 does not produce the same result. Instead it returns the value of Now.

Obviously there is something I am missing about how MOD works in VBA. What do I need to change to get the same result that the Excel MOD Function produces?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2023-02-09T01:43:42+00:00

    Hi. The vba MOD function works on Integers.

    Now ~ 44965.86 Mod 1 returns a date 1/1/ 1900.

    I think you just need:

    EventTime = Now + TimeSerial(0, 10, 0)

    = = =

    Worksheet: =MOD(0.5,1) -> 0.5

    vba: 0.5 Mod 1 -> 0

    1 person found this answer helpful.
    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-02-09T05:05:28+00:00

    Yes, TimeSerial works nicely. Thanks!

    0 comments