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.
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)
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.
Neale Blackwood CPA runs A4 Accounting, providing Excel training, webinars and consulting services.