DMW logo

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 by David Wallis 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:

Summing time in Excel

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:

Excel 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

Formatting for Excel sum time calculation

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.


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.

“Thanks for the Excel Response for Summing Time Values — Worked Perfectly”

AT, Wisconsin (2010)