Performing mathematical operations on time and date is impossible, as they’re not numbers. Luckily, you can use functions to convert time and date to numbers and then convert them back to their original formats when you’ve done your math on them.
The CONVERT function allows you to convert time to hours, minutes, or seconds and then convert it back to its time format. In comparison, you can use DATEVALUE and TO_DATE to convert date to its numeral date value, and vice versa. Read on to learn about these functions, and see them in action with some simple examples.
Converting Time Into Hours, Minutes, and Seconds
In Google Sheets, time is a unit that shows how much has passed since the day began. Every 24 hours makes up a full day, and any less than that is less than a day. So when you want to convert this time to hours, minutes, or seconds, you’re actually converting a day to those units.
This is valuable information, as it enables us to use the CONVERT function on time formats.
=CONVERT(value, start_unit, end_unit)
The CONVERT function converts the value in the start_unit to the end_unit and returns the new value. Google Sheets supports a wide range of units, and the time units are as below:
| Time | Unit |
|---|---|
| Year | “yr” |
| Day | “day”, “d” |
| Hour | “hr” |
| Minute | “min”, “mn” |
| Second | “sec”, “s” |
As an example, we have the duration it took for a trucker to drive from town A to town B with his truck. The goal is to convert this time to minutes using the CONVERT function.
- Select the cell where you want to show the time in minutes. For this example, that will be C2.
- In the formula bar, enter the formula below:
=CONVERT(A2, "day", "min")
This formula instructs the CONVERT function to recognize the value in cell A2 in days, and then convert it to minutes. As explained before, time in Sheets is technically a day unit, where 24 hours make up a full day.
- Press Enter. Sheets will now convert the time into minutes.
You can also put hr or sec in place of min to convert the time into hours and seconds, respectively.
Converting Hours, Minutes, and Seconds into Time
Just like you converted time into minutes, you can also convert minutes into time. You can do this with the CONVERT function in the same fashion. The difference here is that the returned value will be a number and not in time format. You can fix this by changing the cell formatting to time. Let’s see this work in action.
In a similar example, we have a race driver’s lap time in seconds and want to convert it to time format using the CONVERT function.
- Select the cell where you want to display the time format. That is cell C2 in this example.
- Click Format in the Sheets menu.
- Navigate to Number and then select Duration.
- Now go to the formula bar and enter the formula below:
=CONVERT(A2, "s", "day")
This formula calls on the CONVERT function and instructs it to convert the value in cell A2 from seconds to days, and then displays the results.
- Press Enter. Sheets will now display the seconds in hours, minutes, and seconds.
Converting Dates to Date Value and Vice Versa
Similar to Excel, Google Sheets uses a sequential database of numbers to store dates to be able to perform calculations on them. This number begins from 0 and goes all the way up. Zero is the last day of the year 1899, and each day after is a number higher. For instance, 1/1/1990 is 1 in this sequential database.
The number value of each date in Sheets’ database is referred to as the date value. You can convert a date to its date value and vice versa using the DATEVALUE and TO_DATE functions, respectively.
=DATEVALUE(date)
Gets the date value of the date and displays it.
=TODATE(datevalue)
Shows the date for a date value in date format.
Let’s use a simple example to better understand the use of these two functions. In this sample spreadsheet, we have the target dates of some milestones for a project. Due to unforeseen complications, these milestones have been delayed by some days. The goal is to calculate the new target dates for each milestone.
The method we’re going to use here is to convert the dates to date values, add the days delayed to the date value to get the new date value, and then convert the new date value to the new date. Let’s get to it!
- Select the first cell in the column you want to show the date values. That will be cell C1 in this example.
- Go to the formula bar and enter the formula below:
=DATEVALUE(B2)
This formula will display the date value for the date in cell B2.
- Press Enter.
- Grab the fill handle on that cell and drop it on the cells below to get their date values as well.
Now that you have the date values, let’s add the number of days delayed to them:
- Select the first cell in the column you want to show the new date values. That will be cell E1 for this example.
- Enter the formula below in the formula bar:
=C2+D2
This will add the days delayed (in cell D2) to the old date value (in cell C2) and give you the new date value.
- Press Enter.
- Grab the fill handle and drop it on the cells below to get the new date value for them.
Finally, it’s time to convert the date values to dates:
- Select the first cell in the column where you want to show the new dates. Cell F1 for this example.
- Go to the formula bar and enter the formula below:
=TO_DATE(E2)
This formula will convert the new date value (in cell E2) to date.
- Press Enter.
- Grab the fill handle and drop it on the cells below. You now have the new target dates for each milestone!
Time and Date the Way You Want Them
You now know how to convert time to different units like hours, minutes, and seconds. This lets you get a better perspective and perform mathematical operations on it, since it’s converted into a number.
Google Sheets uses a sequential order to store dates, where each day is a number. You can now convert a given date to its date value, perform any mathematical operation you want on it, and then convert it back to date format. Knowledge is indeed power.
Read Next
About The Author
