How to Add, or SUM, Time Values in Microsoft® Excel

How in Excel to add, or sum, a column of time values in the hh:mm format so the total appears in same format.

Thanks to everyone who in 2023 has made a donation to this website. Much appreciated.

Last updated on 2022-01-27 by David Wallis.

Question

“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?”

Response — With Proviso

The formula for the total you created with Σ — AutoSum — is fine.

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

DMW Consultancy Ltd 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.