Summing hh:mm Time Values in Microsoft® Excel
Last updated on 2020-05-26 by David Wallis.
“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 you created with Σ — AutoSum — is fine.
For old versions of Excel, pre-ribbon, all you need do is apply an appropriate format to its result:
- Select the cell containing the total
- From the Format menu, choose the Cells command
- From the Category list, select Custom
- In the Type box, input the format [h]:mm (note the use of square brackets). If you want to show seconds in your total, input [h]:mm:ss (this format is listed in Excel’s Custom formats)
- Click OK.
For versions of Excel with the ribbon, all you need do is apply an appropriate format to the total formula:
Find the format on the ribbon's Home tab, under Number, by choosing More number formats from the drop-down list to display the Format Cells dialog box:
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 as 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
If you’re going to make use of Tips in any of your professional or commercial work, then please consider making a payment in recognition of the time spent publishing this website, keeping it free of advertising.
To make a contribution by PayPal in GBP (£ sterling) —
To make a contribution by PayPal in USD ($ US) —
To say how much you want to pay and to receive
a tax invoice for a GBP (£ sterling) contribution —
Thanks, in anticipation.
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.