How To Set Up Time Calculation In Excel

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

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

Thanks for donation

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

Question

"Nosotros have a cavalcade of cells in an Excel spreadsheet containing times like, 3:15, relating to the time spent on a task. At the lesser 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 lot created with Σ — AutoSum — is fine.

To reach the upshot you want, you need appropriate formats for the time values, 1 for the recorded times and another for the total:

Summing time in Excel

If you input a fourth dimension spent by typing iii:15 (iii hours and 15 minutes), then information technology's likely that Excel will have accorded the hh:mm format to the jail cell into which you typed.

To establish whatsoever format you need, in the Home tab on Excel's ribbon, at the corner of the Number grouping, click the ⇲ button to open the Format Cells dialog box:

Excel format cells dialog box

Piece of 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 to a higher place 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 lot'll demand to adjust the format of Column B

Formatting for Excel sum time calculation

The input into Jail cell B2 is xxx:00. In the formula bar, y'all may run across Excel considering this input to be 01/01/1900 06:00:00.

Excel'south view is that January 1st 1900 is the first twenty-four hour period in its internal agenda, 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 capeesh that when inputting combinations of these.

Your Support for DMW TIPS

Please support this website by making a donation to aid keep information technology free of advertisement and to help towards toll of time spent adding new content.

To make a contribution by Pay Pal in GBP (£ sterling) —

To make a contribution past Pay Pal in USD ($ Usa) —

Cheers, in anticipation.

Disclaimer

DMW Consultancy Ltd does non accept any liability for loss or impairment to information to which whatsoever techniques, methods or lawmaking included in this website are practical. Back up your data; examination thoroughly before using on live data.