[Excel] Why is the working hours not matching the actual working hours and the calculation is so short !? Technique to display the total hours correctly in Excel

Default formatting does not display totals over 24 hours correctly

Managing working hours with Excel is not difficult at all. For example, if you add up the working hours of a day calculated from the working hours of each sunrise and the leaving time for one week, you can calculate the total working hours of that week. You don't need complicated functions for the formulas you enter in the cells.

However, even if you enter the correct formula in the cell, if it is left as it is, the correct value will not be displayed when the total time exceeds 24 hours.

As an example, I created a timesheet with a column to total the working hours for one week.

If you add up the working hours displayed in column E, you should be able to find the working hours for one week, so enter the formula "= SUM (E3: E9)" (①) in cell G1. However, the calculation result was displayed as "16:51" (②). This is clearly shorter than the actual working time, isn't it?

So how do you get the correct value for the total working hours for a week?

This time, I will explain the technique to correctly display the total time over 24 hours as in this example.

Let's fix the timesheet where the total working hours are not displayed correctly so that the correct value is displayed.

After clicking cell G1 (①) in the previous timesheet example, click the arrow icon (②) displayed at the bottom right of the [Numeric] group on the [Home] tab of the ribbon.

The [Format Cell] dialog box will be displayed. Click [User Defined] (④) in [Category] on the [Display Format] tab (③).

Correct "h: mm" displayed in [Type] to "[h]: mm" (⑤) (all half-width characters), and click [OK] (⑥) to close the dialog box.

This operation changes the cell display format so that the total working hours for one week is correctly displayed as "40:51" (⑦) in cell G1.

In Excel, simply summing the numbers entered in the time display format automatically changes the display format of the cell that displays the total, and the result is displayed as a 24-hour time. Therefore, if you want to display the time less than 24 hours, you can leave it as it is, but if you want to display the total elapsed time exceeding 24 hours, display "[h]: mm" as in this example. You need to set the format.

This time, I explained the technique to change the cell formatting to "[h]: mm" in order to correctly display the total time over 24 hours.

As in the timesheet example, you may see the correct formula in a cell, but not what you want. In such cases, you may be able to see the correct results by changing the cell display format. Please try various display formats.

You may also not notice that you are seeing something different than what you are looking for, as you will not see the error if the correct formula is entered in the cell. It is also important to have some time to look back visually once you have created the material.