Forum Discussion
summing h:mm columns
How everyone.
How do I correctly sum various h:mm columns? Straight SUM function of the columns not producing the correct total?
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.
15 Replies
Hi, I am trying to add a column of hours and minutes and have looked for the [hh]:mm option in cell format but it's not there. I tired typing it in myself but it's not working - just giving me ######### result. Can you help?
FidelmaSDCCC , it shall work if only you have no negative time as result.
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
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.
Hi. I am trying to autosum a column of hours and minutes and looked for the [hh]:mm option in cell formatting but it's not there. I typed it in myself, but its still not working and all I am getting is ######### result - can you help?
Thank you for the excellent explanation. However, this doesn't seem to be working when using the AutoSum capability. When adding a column of cells, (A1 through A16) It works correctly if I do individual cells (i.e., =sum(A1+A2+A3 ... etc.), but if I use =sum(A1:A17) it comes up with zeroes. Any thoughts on what is occurring?
Most probably you have texts which looks like time. Time is actually number is behind.
Applying arithmetic operations to such texts Excel automatically converts them to numbers and performs arithmetic. But SUM ignores all texts, result will be zero if no one number in the range.
I have tried to use the (hh):mm format but my time is still showing incorrectly. I am trying to add this column of hours?
What am I doing wrong
09:30 08:00 00:00 00:00 08:30 (02):00 It should be [hh]:mm with square brackets [ ] instead of (hh):mm
