Excel Yourself: Summing hours in Excel

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

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

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

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