How to Add, or SUM, Time Values in Microsoft® Excel
“We have a column of cells in an Excel spreadsheet containing times, like 3:15, relating to the time spent on a task. At the bottom of the column I've used AutoSum to add up the times, but the result appears as either a date or funny number. What do I do to get a total in hours and minutes?”
Last updated on 2024-05-09.
Response — With Proviso
Your formula for the total you created with Σ — AutoSum — is fine.
However, to achieve the result you want, you need appropriate formats for the time values, one for the recorded times and another for the total:
If you input a time spent by typing 3:15 (three hours and 15 minutes), then it's likely that Excel will have accorded the hh:mm format to the cell into which you typed.
To establish any format you need, in the Home tab on Excel's ribbon, at the corner of the Number group, click the ⇲ button to open the Format Cells dialog box:
Work on Custom in the Category list. If the format you need is not already listed, then input it into the Type box.
The Proviso
The solution described above works provided each and every Time Spent is less than 24 hours. If any of them are likely to be longer than 24 hours, then you'll need to adjust the format of Column B
The input into Cell B2 is 30:00. In the formula bar, you may see Excel considering this input to be 01/01/1900 06:00:00.
Excel's view is that January 1st 1900 is the first day in its internal calendar, worth 24 hours.
Excel Formulae and Formats for Time Differences carries a full explanation of how Excel accommodates dates and time and how we need to appreciate that when inputting combinations of these.
Your Support for DMW TIPS
Please support this website by making a donation to help keep it free of advertising and to help towards cost of time spent adding new content.
To make a contribution by PayPal in GBP (£ sterling) —
To make a contribution by PayPal in USD ($ US) —
Thanks, in anticipation.
Disclaimer
David Wallis does not accept any liability for loss or damage to data to which any techniques, methods or code included in this website are applied. Back up your data; test thoroughly before using on live data.