How to Sum Elapsed Hours and Minutes in Microsoft Access
Question details
The user needs to calculate and display the total elapsed monthly duration (hours and minutes) in an Access report.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Creating reports in Microsoft Access where daily or weekly task durations are tracked, and the monthly total needs to be displayed even when it exceeds 24 hours.
- Observed behavior
- The Access report incorrectly displays only the minutes from the last recorded entry instead of calculating the true total monthly elapsed duration.
Ensure that your database table uses standard number fields for 'Hours' and 'Minutes', or a valid Date/Time field, before applying the calculation.
Convert and Sum Durations using the TimeElapsed Function
This method converts separate hour and minute fields into a fractional day format before summing, which accurately handles time totals exceeding 24 hours.
Standard time formatting in Access resets after 24 hours. By converting the durations to a numeric value (fractional days) first, you can sum the total amount of time and then format it properly to show the correct elapsed duration.
Open your Access report in Design View and locate the specific fields where 'Hours' and 'Minutes' are stored.
Select the text box where you want the total to appear. In the Property Sheet, set the Control Source property to the formula: =TimeElapsed(Sum(([Hours]+[Minutes]/60)/24),"nn")
If your durations are stored in a single Date/Time field instead of separate fields, change the Control Source formula to: =TimeElapsed(Sum([DateTimeField]),"nn")
Switch the report to Print Preview or Report View to verify that the elapsed time calculates properly and successfully displays totals exceeding 24 hours.
Need a Free Alternative to Microsoft Office?
While Microsoft Access handles complex database queries, everyday tasks like data analysis, document creation, and presentations are much easier with WPS Office. Enjoy a lightweight, free alternative that perfectly handles standard Office tasks and formatting.
- 1. Download the installer: Visit the official WPS website and click the download button to get the free installation file.
- 2. Install WPS Office: Run the downloaded setup file and follow the simple on-screen instructions to install the suite on your device.
- 3. Start tracking time: Open WPS Spreadsheet and use standard cell formatting like '[h]:mm' to easily sum elapsed hours over 24 hours.
Frequently Asked Questions
Why does my Access report only show the time from the last record?
This usually happens when the control source is set to display the time field directly instead of using an aggregate function like Sum() across the entire recordset. You must wrap your fields in a Sum() function in the report footer.
How do I format time to show more than 24 hours in Access?
Standard Access time formats cap at 24 hours. To display elapsed time beyond 24 hours, you must calculate the total time as a numeric value (such as fractional days) and use a custom expression or VBA function to format it as a string of hours and minutes.
Can I do elapsed time calculations easily in spreadsheet software?
Yes, in spreadsheet software like WPS Spreadsheet or Microsoft Excel, you can easily calculate and display elapsed time exceeding 24 hours simply by highlighting the total cell and applying the custom number format '[h]:mm'.
