Do you have numbers / decimals in your spreadsheet that you want to convert to duration format? In this lesson, I am going to show you not only how to convert cells to duration format, I will show you how to convert decimals into duration format so that the original number in the cell represents the number of hours.
Sometimes you will have numbers / decimals in your spreadsheet cells, and you will already know that the number you have entered is is the exact number that will yield the exact duration that you want, and in this case all you need to do to convert to duration is change the cell to “Duration” format.
But sometimes you will have numbers entered into the cells, where if you only convert the cell format the duration will not display as you want, and you’ll need to do a bit of simple math to convert the numbers to hours.
To convert decimals to durations in Google Sheets, follow these steps:
- Select the cell or cells that you want to convert to duration format
- Click the “More formats” menu on the top toolbar
- Click “Duration”
- If you want to convert numbers / decimals to hours, divide the number by 24
After following these steps, the numbers will display in duration format.
This lesson focuses on converting decimals to duration, but you can click here if you want to do the opposite and convert durations to decimals.
Or click here to learn how to convert between duration, seconds, minutes, and hours.
How to convert numbers / decimals to duration (hours)
In this example, I will show you how to convert decimals to hours in duration format. For example, if the number 0.25 is entered into cell A2, and if you want this number to represent 1/4 of an hour (0.25 hours), simply divide the number by 24 to convert numbers to duration in hours. Then change the cell format to “Duration”.
To convert decimals (numbers) into duration represented as hours in Google Sheets, enter a formula like this into the cell that divides the number by 24 (=A1/24). Then change the cell format to “Duration” (click the “More formats” menu and then click “Duration”).
In a spreadsheet, one full day or 24 hours is equivalent to the number one. 1 = 24:00:00
1= 1 Day = 24 hours = 24:00:00
Decimal / 24 = Duration hours
So if we want to convert numbers / decimals to hours, we simply divide by 24, as shown in the image below. (Use a forward slash (/) to divide in a spreadsheet)
Make sure that you convert the cell itself to “Duration” format so that the value actually displays as a duration.
=A2/24
As you can see in the image above, cell A2 contains the number 0.25, and cell B2 divides cell A2 by 24. After cell B2 is converted to duration format, it displays 0:15:00 (15 minutes), which is equivalent to a quarter of an hour or 0.25 hours.
Converting from decimal to duration
Now let’s go over a basic example of simply changing the cell format to “Duration”. Sometimes you will not need to use any math to convert the decimal to duration. Or if you used a formula like in the previous example, you can use this same method to ensure that the cell containing the formula displays in duration format.
If the number that is entered into the cell is already the exact number that will yield the duration that you want, then there is no need to use any math, and you can simply change the format of the cell to make the decimal display in duration format.
This is common when you have durations that were entered into a spreadsheet, that later got converted into number format accidentally. This can also happen when you import data into your spreadsheet that has columns showing durations, but when the column that you imported into is in number format. In both of these cases, we know that the number in the cell is already the exact value that should be entered to represent the correct duration, and no mathematics is needed to convert.
To convert cells to duration format, select the cell or cells that contain the numbers to be converted, click the “More formats” menu on the top toolbar, and then click “Duration”.
As you can see in the image above, the number 0.25 is entered into cell A2. And as you can see in the image below, when this decimal is converted into duration format, without doing any mathematics, the equivalent duration is 6:00:00.
This is because the number 0.25, actually represents 0.25 days, which is 1/4 of a day or 6 hours. This brings us to our next example, where we can convert numbers to hours.
