Using Paste Special to convert hours into time
Here is a quick and easy method to convert hours into time on the worksheet. Proceed as follows:
-
Open the Date and Time.xlsx workbook and navigate to the PASTE
workbook.
- In this workbook, we have a list of times employees and their time worked per file on the system. The values are entered as hours. We cannot apply the time format to the time worked, as this will result in 00:00:00 for every value. To fix this, we would need to divide the hours worked by 24 in order to format this correctly to reflect time as 1 day is equal to 24 hours.
- Enter the value 24 in cell A11.
- Select cell A11 and copy it so that it is retained on the clipboard.
- Select the values on the timesheet—namely, D2:F9.
- Click the drop-down arrow on the Paste button, then select Paste Special…, as illustrated in the following screenshot:
Figure 11.24 – Paste Special…
-
Choose Divide, to divide by the number of hours in a day (24), then click on OK
to confirm.
- Format the range D2:F9 as Time
Here are a few time functions to boost your knowledge:
Table 11.2 – Time functions in Excel
We will look at a few time functions in the following topics.
Separating time from date/time
If a date and a time are combined into a cell, we can extract the time from the date. In cell B23 of the TIME worksheet (Date and Time.xlsx), we have entered the =NOW() function to add the date and the time in one single cell. Carry out the following steps to extract the date or time, from the formula:
- Click on cell C23—this is where we would like to build the formula to extract the date.
-
Type =INT(B23), then press Enter to confirm. INT means integer, so it will exact the whole number (the date) from the contents of cell B23. Format cell C23 in Date format so that it correctly displays the date in the cell, as illustrated in the following screenshot:
Figure 11.25 – The INT function
-
To display only the time, subtract the date from the cell that contains the date/time combination. Click into cell D23 (this is the cell in which you require just the
time value).
- Type =B23-C23, then press Enter to confirm. Format the cell to display the Time
value, if necessary.
Let’s learn how to change data imported as text into a time format.
Converting text to a time format
Sometimes, we may receive datasets that are in a text format or on import do not represent as a time format in Excel. We can fix this by using the TIMEVALUE() function. Here’s how to go about this:
- Open the workbook named Date and Time.xlsx.
- Select the TIME worksheet. Note that the data in cells D12:D17 is aligned to the left of the cell, and therefore is not representing as time. The number format would display as Text or General format in this case.
- To correct the format, let’s change the format to Time. Notice in the following screenshot that the time is still positioned to the left of the cell:
Figure 11.26 – Changing the format to Time
- If we try to add up the time to find out what the total time would be, it would return a 00:00:00 time value to the right of the cell, as illustrated in the following screenshot. Excel is therefore still associating the data in cells D12:D17 as text:
Figure 11.27 – SUM value displaying as 00:00:00
-
To convert the text to time so that it is correctly interpreted as time in Excel, we can make use of the =TIMEVALUE() function. Click on cell E12 on the TIME worksheet, as illustrated in the following screenshot:
Figure 11.28 – TIMEVALUE function to convert text to time
-
Press Enter to confirm, then drag the formula down to fill in the rest of the time values. Be sure to format the cells as Time.
As mentioned in a previous topic, Excel does not take into account hours that exceed the 24-hour format. When adding time, this could be problematic and is often a cause of time being incorrectly calculated on a timesheet without even apprehending it!
