How to Convert Milliseconds to [h]:mm:ss in Access VBA
Question details
The user needs to convert a duration stored in milliseconds into an [h]:mm:ss elapsed-time format using Microsoft Access VBA, specifically ensuring that durations over 24 hours are handled correctly.
![How to Convert Milliseconds to [h]:mm:ss in Access VBA](https://res-academy.cache.wpscdn.com/upload/seo-engine-admin/2026-11-02/b1407ccfb2ff677afd20979ecb607b9f.png)
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Writing a VBA script or query where time durations are tracked in milliseconds and must be displayed in a human-readable format of hours, minutes, and seconds.
- Observed behavior
- A script or expression is needed to correctly calculate the millisecond value into fractions of a day and format it so hours can exceed a standard 24-hour limit without rolling over.
Ensure you have your Microsoft Access database open and access to the VBA editor. It is helpful to identify the exact variable, field, or control name that contains your millisecond values before applying the formula.
Convert and Format Milliseconds Using Division
Use a mathematical formula to transform your millisecond data into days, and then format the result to properly display elapsed hours, minutes, and seconds.
Microsoft Access stores date and time values internally as fractions of a single day. To correctly format milliseconds, you must first convert that value into a daily fraction by dividing it by the total number of milliseconds in a 24-hour period.
Identify the specific field or variable in your Access VBA code that holds the duration in milliseconds.
In your VBA script or query expression, divide the millisecond value by 86,400,000. This number represents the total milliseconds in one day (24 hours × 60 minutes × 60 seconds × 1000 milliseconds).
Format the resulting decimal value using the string '[h]:mm:ss'. The brackets around the 'h' instruct Access to display elapsed hours, preventing the time from resetting to zero once it surpasses 24 hours.
Try WPS Office for Your Data and Spreadsheet Needs
While Microsoft Access handles complex relational databases, many data tracking and time conversion tasks are easier to manage in spreadsheets. WPS Office provides a free, lightweight, and highly compatible alternative to Microsoft Office, complete with advanced spreadsheet functions for handling time data effortlessly.
- 1. Download and Install WPS Office: Get the free productivity suite from the official WPS website and install it on your device.
- 2. Open Your Data in WPS Spreadsheet: Launch WPS Spreadsheet and import your CSV or Excel files containing the time or duration data.
- 3. Apply Custom Time Formatting: Select the cells with your time values, right-click and choose 'Format Cells', then apply the '[h]:mm:ss' custom format directly.
Frequently Asked Questions
Why use [h] instead of h in the time format?
Using [h] with brackets forces the application to display total elapsed time. If you use a standard 'h', the counter resets to 0 after reaching 24 hours. The bracketed format ensures that 25 hours is displayed accurately as '25:00:00'.
Where does the number 86,400,000 come from?
It represents the total number of milliseconds in a single day. The calculation is 24 (hours) × 60 (minutes) × 60 (seconds) × 1000 (milliseconds).
Can I use this same formatting method in Microsoft Excel or WPS Spreadsheet?
Yes, the custom format string [h]:mm:ss works exactly the same way in Excel and WPS Spreadsheet for displaying elapsed time that is stored as a fraction of a day.
