Excel Yourself: Summing hours in Excel

Excel Yourself

If you use Excel for hour calculations, you may have found that adding up hours isn’t as easy as you’d expect.

Because hours are treated as a fraction of a day, when you add them up they won’t go past 24 hours.

The cell D8 in figure 1 shows the problem.

Figure 1

Figure 1

Note the hours in columns B and C have been entered using the full colon, so 15:00 is 3pm.

Cell D2 has the formula =C2-B2 in it and this has been copied down into the cells down to cell D6. Excel handles the subtraction of hours calculation correctly. 

Cell D8 has a SUM function =SUM(D2:D6)

Figure 2

Figure 2

It is showing 3:15 or 3 and a quarter hours. The hours actually add up to 27 and a quarter hours. Excel is only displaying the excess hours above 24.

To have Excel display the hours correctly you just need to use a custom number format on the total cell D8.

The custom number format is [h]:mm

The total is now working (Figure 3). It was a formatting issue.

Figure 3

Figure 3

Neale Blackwood CPA runs A4 Accounting, providing Excel training, webinars and consulting services.

How-to articles, sample spreadsheets and more

Excel yourself

Combine annual and monthly data in Excel with these simple steps


Making data relationships work

Easy steps to make data Relationships work in Excel


Excel Yourself

Easy tips for matching salary to job class in Excel