Mr. Han's own book has been included in the national 13th and 14th Five-Year Vocational Education Planning textbooks
Question
At the end of the year, a department needs to roughly calculate the overtime expenses to be paid based on the overtime hours, but when the overtime hours are summed, a problem occurs, and the example data and error results are as follows:
There is nothing wrong with the formula "=sum(b2:b11)" itself, but the calculation is clearly wrong.
The reason for the error is that it does not display properly for more than 24 hours.
Resolution
The correct data can be derived by modifying the formula.
Enter the formula:
=text(sum(b2:b11),"[hh]:mm:ss")
The meaning of this formula is to convert the sum of all overtime hours into the format of "hours:minutes:seconds" with the text function, where the hh representing hours is written in the format [hh], and the duration of more than 24 hours can be displayed correctly.
If you don't want to use the text function, you can use itCustom formattingmethod implementation.
The original formula "=sum(b2:b11)" remains unchanged, right-click on the result cell, select "Custom" in "Format Cell", and enter the format type as "[hh]:mm:ss". As shown below:
End of this article.