VBA & Macro Problems

How to Calculate Excel VBA Time Using TimeSerial Instead of Mod

Tauseeq Magsi Oct 16, 2026 868 views

Question details

The user needs to accurately calculate and schedule time using Excel VBA, but the standard VBA Mod operator is stripping away the time values by discarding fractions.

How to Calculate Excel VBA Time Using TimeSerial Instead of Mod

Product: Excel VBA
Device & OS: not provided
Scenario: Scheduling future macro events or calculating precise time values using VBA code.
Observed behavior: The VBA Mod operator evaluates decimal time values to 0 (e.g., 44965.86 Mod 1 returns 0) because it only processes integers, unlike the worksheet MOD function which correctly handles time fractions.

Before you start

Ensure you save a backup of your macro-enabled workbook before modifying your VBA event scheduling code, as incorrect time loops can cause Excel to freeze.

Solution 1Recommended

Use TimeSerial to Schedule and Calculate VBA Time

Replace the VBA Mod operator with the TimeSerial function to safely and accurately handle time additions without losing the fractional data.

In Excel, dates and times are stored as serial numbers where the integer represents the date and the fraction represents the time. The VBA Mod operator mathematically rounds numbers to integers before evaluating. Therefore, an expression like '44965.86 Mod 1' evaluates as '44966 Mod 1', resulting in 0. This completely discards your time portion.

To avoid this limitation when scheduling events, you should use the built-in TimeSerial function, which handles hours, minutes, and seconds safely.

Open the VBA Editor

Press 'Alt + F11' on your keyboard to open the Visual Basic for Applications (VBA) editor.

Locate the Event Code

In the Project Explorer panel, double-click the module or worksheet containing the time calculation or Application.OnTime event.

Remove the Mod Operator

Find the line of code attempting to wrap or calculate time using 'Mod 1' and delete that specific mathematical operation.

Implement TimeSerial

Rewrite your time scheduling logic using the TimeSerial(hours, minutes, seconds) function. For example, to schedule an action 10 minutes from the current time, type: EventTime = Now + TimeSerial(0, 10, 0).

Use TimeSerial to Schedule and Calculate VBA Time

Correct Time formatting: Using TimeSerial(0, 10, 0) guarantees that VBA calculates exactly 10 minutes as a fraction of a day and perfectly adds it to the 'Now' timestamp.

Advanced Macro Support

Write and Run VBA Macros Seamlessly in WPS Office

WPS Spreadsheet offers robust support for VBA macros, allowing you to execute scripts, schedule events with TimeSerial, and automate your entire workflow just like in Microsoft Excel.

1. Download WPS Office: Download and install the latest version of WPS Office, ensuring you select the package that includes VBA support.
2. Open your Macro Workbook: Launch WPS Spreadsheet and open your existing .xlsm file containing the time calculation macros.
3. Access the Developer Tools: Navigate to the 'Developer' tab on the top ribbon and click on 'VBA Editor' to view your code.
4. Run your Script: Input your updated TimeSerial macro code, save the module, and click 'Run' to execute the scheduled event perfectly.

Fully compatible with Microsoft Excel VBA syntax and logicAccurately handles TimeSerial, Now, and date/time mathematical functionsFree and lightweight spreadsheet alternative with familiar UISeamlessly opens, edits, and saves .xlsm macro-enabled workbooks

microsoft office alternative - wps office

Frequently Asked Questions

Why does the VBA Mod operator return 0 for time values?

The VBA Mod operator truncates numbers to integers before performing the modulo operation. Since Excel time is stored as a decimal fraction of a day (e.g., 0.5 for 12:00 PM), the decimal portion is rounded off, resulting in 0.

Can I use the worksheet MOD function inside VBA instead?

Yes. If you strictly need the mathematical modulo operation on decimals, you can call the worksheet function within VBA by using 'Application.WorksheetFunction.Mod(value, divisor)' rather than the native VBA Mod operator.

How do I add precise minutes to the current time in a VBA macro?

You can add minutes by combining the 'Now' function with 'TimeSerial'. To add 15 minutes to the current time, you would write 'Now + TimeSerial(0, 15, 0)'.

What is the difference between Now and Time in VBA?

The 'Now' function returns both the current system date and time (e.g., an integer plus a decimal), whereas the 'Time' function returns only the current system time (only the decimal portion representing the time of day).

Excel & Data Problems