Working with time
When working with time in Excel, we need to ensure that we format cells correctly. This is extremely important as Excel does not display hours over 24 (1 day), seconds greater than 60, or minutes greater than 60. Hours are always fractions of a day in decimal
presentation, whereas dates are whole numbers. A whole number can never be interpreted using hours or minutes when working in Excel. Decimal numbers are units of time between 0 and 1—for example, 0.25; 0.5; 0.45; 0.35.
So, Excel by default does not display hours in the 24-hour format. When we need to display hours in the 24-hour format, we will need to change the format of the cells. Let’s see how this is achieved in the following example.
Displaying hours as time
In the Date and Time.xls spreadsheet, we have entered 0.25 in cell B19 on the TIME worksheet. To display this value in the 24-hour format, we will need to format it to the Time number format, as illustrated in the following screenshot:
Figure 11.23 – Changing the display of values to the 24-hour format
The time is displayed as 6 hours as it is a quarter of the day (1 day = 24 hours; a half-day is 12 hours; a quarter-day is 6 hours). If you need to display hours over the 24-hour format, you will need to change the format so that it is customized to include a square bracket around the h format. Let’s look at an example, as follows:
-
Open the workbook named Date and Time.xlsx and navigate to the
TIME worksheet.
- Cells A1:G9 display time worked per employee on files within the business. The Total Time value is represented in column G. If we look closely at the sum calculation, we will notice that column G is not in time format.
- Format cells G2:G9 to the Time format. Notice that it is still not displaying the time over the 24-hour period correctly.
- Select G2:G9, then visit the Custom category in the Format Cells dialog box.
-
Type the following custom format into the Type: field as [h]:mm. Click on the OK
button to confirm.
- The cells are now showing the correct display for times greater than 1 day.
To display a whole number such as 29 as time, you would divide the number by 24 as follows: =29/24. This would present an answer of 1.208333333. You would then format the cell as Time to display 05:00:00.
Now that you have learned to understand how Excel interprets time, let’s master a quicker method in the next section.
