Back to Index

EXCEL 2010-2007 : ADDING HOURS

I add hours (5 times 8:00) and I get 16:00 instead of 40:00. How to correct this ?

Excel table showing day hours where the total is not incorrect (16 instead of 40)

The K11 cell (a standard sum) shows an incorrect result - 16:00 instead of 40:00.

This comes from how Excel works with hours and minutes : it does the addition goes by "rounds", when it gets to the whole number (60 for minutes and 24 for hours) it drops it. Indeed 5 times 8:00 is equivalent to a whole day of 24 hours + 16 hours.

To correct the problem, it is necessary to order a more "decimal" addition.

The solution ? Use the custom format [h]:mm:ss

Select the cell (here K11)
HOME Tab - NUMBER Group - Dialog Box Launcher - NUMBER Tab - CUSTOM Categorie

Image montrant le format-nombre qui corrige le problème de l'addition précédente


You may change the format by removing the :ss (to hide the seconds)
If you work with very, very large hours numbers, you can also add 1 or 2 h between the brackets.

© Copyright : Office Doc / Web development by Office Doc