Time tracking in Excel: how to add hours correctly
Go to the cell where you will be doing the summation of hours.
Right-click on the cell and select Format cells from the context menu.
In the Type field, change the default value “hh: mm” to “[hh]: mm”.
The brackets ensure that Excel no longer suppresses the hours that go beyond a day.
That is why your time and attendance record gives incorrect sums
Actually, calculating with hours and minutes in Excel is very simple: You write the hours and minutes in the desired cells separated by a colon, ie “8:30” for 8.5 hours and add the individual values.
The result is then only correct as long as the total remains below the value of 24 hours. Because the portion of the sum that goes beyond a day simply falls under the table.
An example: In one week you have accumulated the following hours: 5½, 7, 6½, 7, 4½. This adds up to a working week of 30.5 hours if you add it up in the conventional way.
Excel calculates correctly in itself, but only shows part of the result. The value in cell B7 has the correct numerical value internally. For example, if you reformatted the sum cell B7 as a “number”, the correct value would be 30.5.
| Day | Worktime |
| Monday | 05:30 |
| Tuesday | 07:00 |
| Wednesday | 06:30 |
| Thursday | 07:00 |
| Friday | 04:30 |
| TTL | 06:30 |
The incorrect displayed value of 6 hours and 30 minutes is purely a display problem. Because with the basic formatting of time values, Excel only shows values over 24 hours that go beyond a day. So here 30.5 - 24 = 6.5 hours.
| Day | Worktime |
| Monday | 05:30 |
| Tuesday | 07:00 |
| Wednesday | 06:30 |
| Thursday | 07:00 |
| Friday | 04:30 |
| TTL | 30:30 |
Only by changing the cell formatting to “[hh]: mm” does Excel show the correct hourly total.
If you also work with hours beyond a day in the individual summands, you should reformat these cells in the same way.
I would be happy to know if I could help.
Nikolino
I know I don't know anything (Socrates)
* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.
