««Timesheets in Excel: to add hours correctly

8:30 pm May 13, 20175972

you want to create a timesheets with a hour Calculator in Excel, get you a problems when adding, if total more than 24 hours come together. Learn how to fix the error.

Excel sum shows hours correctly

  1. go to the cell in which you make the summation of hours.
  2. click the cell with the right mouse button and select the item from the context menu format cells from.
  3. , change the field type the preset value “hh: mm” to “[hh]: mm”.

    Excel Format für Zeiterfassung
    With this custom format, Excel displays the correct hour totals.

  4. the brackets cause that Excel no longer fails the hours shares, which extend over a day.

by the way: so add Excel with percent values in fields in .

about your work time recording delivers wrong checksum

actually computing with hours and minutes in Excel is very easy: you write the hours and minutes by a colon into the desired cells separately, i.e. “8:30” for 8.5 hours and add the individual values.

the result is correct but only as long as the sum below the value of 24 hours. Because the proportion of the amount that goes over one day, falls just under the table.

an example: you have a week following hours get together: 5½, 6½, 7, 7, 4½. That adds up to a working week of 30.5 hours, if you commonly Add.

now you see what makes Excel:

Excel falsche Summierung von Stunden
Excel calculates incorrect summation of hours

Excel itself already correctly, shows but only a part of the result map. The value in the cell B7 so internally for the correct value.  If, for example, the total cell B7, you can format as “Number”, the correct value of 30.5 comes out.

the wrong value of 6 hours and 30 minutes is a presentation issue. Because with basic format time values, Excel displays for 24 hours only for values, what goes over a day. So 30.5 24 = 6.5 hours.

only by changing the cell formatting to “[hh]: mm” Excel will display the correct amount of hours.

the right result then looks like:

Excel Stundensumme korrigiert
that appears correct sum of hours

If you in the individual summands with hours over one day, work, just reformat those cells.

last updated on the 19.12.2016 / affiliate links / images from the Amazon product advertising API