Formatting Issues

How to Display Access Report Durations Longer Than 24 Hours

Huma Ashraf Ch Oct 8, 2026 869 views

Question details

The user needs a method to accurately display total elapsed time exceeding 24 hours in a database report without the time value wrapping around.

How to Display Access Report Durations Longer Than 24 Hours

Product: Microsoft Access
Device & OS: not provided
Scenario: Calculating and displaying total elapsed time or durations in a Microsoft Access report.
Observed behavior: Standard time formatting wraps totals after 24 hours because the application stores date and time values as day-based numbers, preventing the display of total elapsed hours.

Before you start

Ensure you have completed all necessary time arithmetic on your numeric fields before converting the final duration to a text format, as text strings cannot be directly used in mathematical calculations.

Solution 1Recommended

Convert the Calculated Duration to Text

Extract the days, convert them to hours, and combine them with the remaining time components to create a text string representing the total duration.

Because Date/Time values are stored as 64-bit numbers representing durations in days, formatting them directly as time will always loop after 24 hours. Converting the final calculated value to text is the most reliable way to show absolute elapsed hours.

Calculate the Total Duration

Perform your standard arithmetic (e.g., adding multiple time fields or using DateDiff) to get the total duration value as a standard Date/Time number.

Extract and Convert the Day Component

Use the Int() function to isolate the total number of days in the duration, then multiply those days by 24 to convert them into hours.

Add Remaining Hours

Use the DatePart function to extract the remaining hours (less than 24) from the duration, and add this to your converted day-hours.

Concatenate with Minutes and Seconds

Format the remaining minutes and concatenate them with your calculated total hours into a text string formula in your report's text box.

Solution 2

Use a Custom TimeElapsed VBA Function

Implement a custom VBA function to automatically format numeric durations exceeding 24 hours into readable text strings like "73:17:15".

Free Microsoft Office alternative

Try WPS Office for Your Everyday Productivity Needs

While Microsoft Access is designed for complex relational databases, WPS Office provides a free, lightweight, and highly compatible alternative to Microsoft Office for your everyday spreadsheets, documents, and presentations. Enjoy a familiar interface and seamless migration without heavy subscription costs.

Fully compatible with Microsoft Excel, Word, and PowerPoint formats (.xlsx, .docx, .pptx).Powerful Spreadsheet tool that handles complex time arithmetic and data analysis with ease.Lightweight software that runs smoothly on Windows, Mac, Linux, and mobile devices.Familiar tabbed interface for easy switching between spreadsheets, documents, and PDFs.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Microsoft Access reset time after 24 hours?

Microsoft Access stores Date/Time values as 64-bit numbers where the integer part represents days and the fractional part represents time. Standard time formatting only displays the fractional part, causing it to visually wrap around when the total duration reaches or exceeds 1 full day.

Can I use the formatted text duration in calculations?

No. Once you convert a duration into a text string (like '73:17:15') to display hours over 24, it can no longer be used in direct mathematical calculations. Always perform your math on the underlying Date/Time numeric value before applying text formatting.

How do I show only total minutes instead of hours?

You can calculate total minutes by converting the entire duration value. Multiply the total duration (which is in days) by 1440 (24 hours * 60 minutes) and use the Int() function to display the total accumulated minutes.

Document & File Issues