VBA & Macro Problems

How to Calculate Daily Work Minutes Correctly in Access VBA

Tauseeq Magsi Sep 27, 2026 869 views

Question details

The user needs to accurately calculate the total daily work minutes using VBA in Access, avoiding errors caused by default date and time formatting.

How to Calculate Daily Work Minutes Correctly in Access VBA

Product: Microsoft Access
Device & OS: not provided
Scenario: Calculating elapsed work times or shift durations that might exceed standard daily hours or cross over midnight.
Observed behavior: Using DateDiff in minutes produces totals that differ from manual calculator results, often due to Access storing date and time values as double-precision numbers and misinterpreting elapsed time as standard dates.

Before you start

Before modifying your VBA code or restructuring your database tables, ensure you have created a backup copy of your Access database to prevent accidental data loss.

Solution 1Recommended

Format Duration as Elapsed Time Using VBA DateDiff

Extract the difference in minutes using the DateDiff function and format it as elapsed time rather than a standard date/time value to ensure accuracy.

Microsoft Access stores date and time values as a 64-bit floating-point number. The whole-number integer portion represents the days, and the fractional (decimal) portion represents the time of day.

When you perform arithmetic on time, durations longer than 24 hours or totals meant to represent raw minutes might be formatted incorrectly by Access if treated as standard dates.

Open the VBA Editor

Press Alt + F11 in Microsoft Access to open the Visual Basic for Applications (VBA) editor.

Write the DateDiff Function

Use the DateDiff function with the interval set to 'n' for minutes. For example: TotalMinutes = DateDiff("n", StartTime, EndTime).

Convert or Format the Output

Instead of applying standard date formatting to TotalMinutes, keep it as an integer, or divide by 60 to display custom hours and minutes (e.g., TotalMinutes \ 60 & " hrs " & TotalMinutes Mod 60 & " mins").

Include Dates for Overnight Shifts

If a work shift crosses midnight, ensure your StartTime and EndTime variables include the full Date value, not just the time, so DateDiff correctly calculates the day crossover.

Format Duration as Elapsed Time Using VBA DateDiff

Handling durations over 24 hours: If the calculated duration exceeds 24 hours, standard time formats (like hh:mm) will reset to zero. Always calculate in base units (like minutes) and use custom math to display the total.

Solution 2

Improve Database Design with a Totals Query

Restructure your tables to eliminate repeating time fields and calculate the daily work minutes dynamically using a Select Query.

Free Microsoft Office alternative

Track Work Hours Easily with WPS Spreadsheet

If dealing with Access VBA and database design becomes too complex for tracking work hours, consider using WPS Office. It offers a free, lightweight alternative with WPS Spreadsheet, allowing you to easily calculate elapsed time, work hours, and daily minutes using built-in functions without writing any code.

1. Enter Your Shift Times: Open WPS Spreadsheet and enter your Start Time in Column A and End Time in Column B.
2. Calculate the Difference: In Column C, enter the formula =B2-A2 to subtract the start time from the end time.
3. Convert to Total Minutes: Multiply the formula by 1440 (the number of minutes in a day) like this: =(B2-A2)*1440, and format the cell as a General Number to see the total daily work minutes.

100% free and lightweight office suite with no subscription requiredFully compatible with Microsoft Excel (.xlsx) formats for seamless migrationBuilt-in TIME functions easily handle elapsed work minute calculationsFamiliar tabbed user interface ensures zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Why shouldn't I store calculated work minutes in my Access table?

It is a standard database design best practice to avoid storing calculated results. Storing only the raw start and end times ensures your data remains accurate and single-sourced. You can always calculate the total minutes on the fly using a query, which updates automatically if the underlying times change.

How does Access internally store Date/Time data?

Access stores date and time values as 64-bit double-precision floating-point numbers. The integer (whole) portion represents the number of days since a baseline date (December 30, 1899), while the decimal (fractional) portion represents the exact time of day.

What happens if a work shift crosses midnight when calculating minutes?

If you only input the time of day, Access assumes both times fall on the same date, resulting in a negative or incorrect duration. To fix this, always include the specific date alongside the time in both your Start Time and End Time fields.

Why do repeating time fields represent poor database design?

Fields named like d1min, d2min, and d3min form what is called a 'repeating group.' This violates database normalization rules, making it extremely difficult to run aggregate queries, add new days, or build scalable reports.

Excel & Data Problems