- slide 1 of 2
Although you thought that you were entering 2 minutes and 35 seconds, if you place the cell pointer in B2, and examine the formula bar, you will notice that Excel thought that you meant 2 hours and 35 minutes, as shown in Fig. 518.
Strategy: One solution is to re-enter all of the formulas, using 0:02:35 as the format. This is probably the fastest method for 11 entries, but if you had hundreds of entries, there is a better way. You can use a series of nested functions to extract the Hour and Minute from the incorrect entry and then use the results in the TIME function.
1) As shown in Fig. 519, in column D, use the =HOUR(B2) function to return the portion of the time before the colon.
2) In cell E2, use the =MINUTE(B2) to return the portion of the time after the colon, as shown in Fig. 520.
3) You will use the =TIME function in cell C2, as shown in Fig. 521. This function requires an hour, a minute, and a second. The hour will be 0. The minute will be the result of the HOUR function in D2. The second will be the result of the MINUTE function in E2. The complete formula is =TIME(0,D2,E2).
4) As usual, don’t be immediately alarmed that the result of the formula is not what you expected. Select the cell. Hit Ctrl+1 to display the Format Cells dialog. On the Number tab, select a time format such as 13:30:55, as shown in Fig. 522.
5) Alternatively, select the Custom category and type a custom number format of M:SS, as shown in Fig. 523.
It always seems funny that you’ve gone through all of the work in this chapter in order to get cell C2 to look exactly like the original cell in B2, as shown in Fig.
524. This is one of the reasons that working with times in Excel is so confusing.
6) As shown in Fig. 525, select cells C2:E2. Double-click the Fill handle (the black square dot in the lower right corner of E2).
7) Using the Fill handle trick will copy the formulas down to all rows with data in the adjacent column B. This will copy the formulas down to the total row, which is not what you want. Delete the formulas from C13:E13.
8) Copy the total formula from B13 to C13. As shown in Fig. 526, you will see that it now returns 31 minutes, a number that is about right.
To correct the format in C13, there are two options. If you had foresight, rather than doing a Copy and Paste from B13 to C13, you could have done Edit – Paste Special – Formulas to copy the formula, but not the number format. It is hard to have such foresight!
9) To fix the format in Fig. 526, select cell C12. Ctrl+C to Copy. Select cell C13, as shown in Fig. 527. Do Edit – Paste Special – Formats – OK.
10) The Paste Special Format command will wipe out the bold formatting in C13, so hit Ctrl+B to bold the cell again.
Additional Details: Before you can delete columns B, D, and E, you need to change the times in column C from formulas to values. Highlight the cells in C2:C12. Use Ctrl+C to Copy. Without changing the selection, use Edit – Paste Special to display the Paste Special dialog. Choose Values and then OK. Just out of curiosity, what about that value in B13? Even if Excel thought we had 31 hours, why is it showing only 7 hours and 42 minutes? Select the cell. Hit F2 to edit the formula. Hit F9 to calculate the formula. You will see that Excel thinks this total is 1.320833, as shown in Fig. 528. This means that 31 hours is about 1.3 days. The default numeric format in B13 was causing Excel to only show the portion of hours in excess of whole days.
After hitting F9 to see the result of the formula, hit the Esc key to return to the formula.
Summary: Beware – some entries can be ambiguous. What may seem like three minutes to you might be interpreted as three hours by Excel. Always select the cell and look in the formula bar to see if Excel is using hours or minutes.
Commands Discussed: Edit – Paste Special – Formats
Functions Discussed: =HOUR(); =MINUTE(); =TIME()