Strategy: The most important part of this solution is to enter the times correctly. In order to have Excel understand that these are minutes and seconds, time should be entered with a leading zero for hours.
1) Enter 0:123:40, as shown in Fig. 494.
2) When you press Enter to accept the cell, Excel will change the value to a decimal portion of a day, as shown in Fig. 495.
3) Select the cell and from the menu, choose Format – Cells. On the Number tab, click the Custom Number format, as shown in Fig. 496.
4) In the Type box, change the Custom Number format from General to [m]:ss, as shown in Fig. 497. You will see in the Sample box that the entry is now formatted with just minutes and seconds. The square brackets tell Excel to display minutes in excess of an hour.
5) Choose OK to close the Format Cells box. Your entry in B2 will appear correctly, as shown in Fig. 498.
Note that in the formula bar in Fig. 498, Excel thinks that 123 minutes is 2:03 a.m. That is because 123 minutes after midnight is 2:03 a.m.
6) Before entering the rest of your time values, copy the numeric formatting from B2 to the rest of the cells in the table. Place the cell pointer in B2. Hit Ctrl+C to Copy. Highlight B3:B14. From the menu, select Edit – Paste Special. In the Paste Special box, select Formats, as shown in Fig. 499, and choose OK.
7) You can now enter the remaining times, using the 0:234:56 format as you type, as shown in Fig. 500.
8) After entering all of the time entries, place the cell pointer in the Total row, as shown in Fig. 501.
9) In the Standard toolbar, click the AutoSum button, as shown in Fig. 502.
10) As shown in Fig. 503, Excel will propose a formula of =SUM(B2: B12). If this is correct, press Enter.
11) Verify that the correct total appears, as shown in Fig. 504.
Gotcha: Be very careful that the total cell is formatted with the square brackets around the M. If the square brackets are not around the M, Excel will show you only the minutes in excess of whole hours. In the case above, 1714 minutes is 28 hours and 34 minutes. With the wrong number format, you would see only 34 minutes and 42 seconds. As shown in Fig. 505, the formula for an average time in B14 is: =AVERAGE(B2:B12)
Summary: The formula to total a column of time entries is intuitive. However, using the proper numeric formats to allow the formula to work is very complex. The key is to use a custom number format with square brackets around the M. Also, you need to enter the times using 0 for the hours.
Commands Discussed: Format – Cells – Numeric
Functions Discussed: =SUM(); =AVERAGE()
Microsoft Excel: Total Minutes That Exceed An Hour
Share to Facebook
Tidak ada komentar:
Posting Komentar