In this Tutorial, I will go into the methods for Adding Time in Google Sheets.
Applying mathematical operations like an addition to add time in Google Sheets is not as intuitive.
This is because time in Google Sheets, like 2:55 PM, is stored differently compared to values in cells.
Adding Time in Google Sheets
To Add Time in Google Sheets do the following:
- Enter a date, time, or datetime into a cell in PC by selecting a cell and by pressing
“ctrl + ;”, “ctrl + shift + ;”, or “ctrl + alt + shift + ;”, on Windows respectively;
or by selecting a cell and pressing
“⌘ + ;”, “⌘ + shift + ;” or “⌘ + alt + shift + ;”, on a macOS device, respectively - Add certain amount of hours to time by using and adjusting the formula:
“=[datetime]+([hours]/24)” - Add certain amount of minutes to time by using and adjusting the formula:
“=[datetime]+([minutes]/24/60)” - Add certain amount of seconds to time by using and adjusting the formula:
“=[datetime]+([seconds]/24/60/60)” - Add any combination of hours, minutes, and seconds to time by using and adjusting the formula:
“=[datetime]+([hours]/24)+([minutes]/24/60)+([seconds]/24/60/60)”
How to Add Time in Google Sheets Example
To simplify this concept, let’s use the date and time “01/29/2020 2:55 PM” as an example.
When entered into a cell in Google Sheets, the date and time “01/29/2020 2:55 PM” is turned into the number “43859.62153”. We classify this value as a “datetime value”.
| DATE | 01/29/2020 |
| TIME | 2:55 PM |
| DATETIME | 01/29/2020 2:55 PM |
| DATETIME VALUE | 43859.62153 |
We can further divide any datetime value into 2 parts, where the whole numbers represent date values, while the decimal numbers represent time values.
| DATETIME VALUE | 43859.62153 |
| DATE VALUE | 43859.0 |
| TIME VALUE | 0.62153 |
Simply put, to add time in Google Sheets, we have to apply the process on the decimal parts, where 1 hour is 1/24th (1/24) of a whole number, 1 minute is 1/1440th (1/24/60) of a whole number, and 1 second is 1/86400th (1/24/60/60) of a whole number.
Keep these insights in mind as we will use them later on.
How to add the current date and time in Google Sheets
Let’s now have a look at how to enter the current date, current time, or current date into a cell.
This is so you can generate a time, date, or both to apply the tutorial on, in case you don’t have a dataset to use.
To add the current date, select a cell then press “ctrl + ;” for Windows or “⌘ + ;” for macOS
To add the current time, select a cell then press “ctrl + shift + ;” for Windows or
“⌘ + shift + ;” for macOS
To add the current date and time, select a cell then press “ctrl + alt + shift + ;” for Windows or
“⌘ + alt + shift + ;” for macOS
Method 1 – Add certain amount of hours to time
=[datetime]+([hours]/24)
You can add the formula above to a different cell. Simply change the [datetime] part to reference the cell where your date, time, or date and time is.
Next, type in how many hours you want to add to the [hours] part.
For example, to add 5 hours to a date and time stored in A1, the formula and result should look like this:
Method 2 – Add certain amount of minutes to time
=[datetime]+([minutes]/24/60)
You can add the formula above to a different cell. Simply change the [datetime] part to reference the cell where your date, time, or date and time is.
Next, type in how many minutes you want to add to the [minutes] part.
For example, to add 75 minutes to a date and time stored in A1, the formula and result should look like this:
Method 3 – Add certain amount of seconds to time
=[datetime]+([seconds]/24/60/60)
You can add the formula above to a different cell. Simply change the [datetime] part to reference the cell where your date, time, or date and time is.
Next, type in how many seconds you want to add to the [seconds] part.
For example, to add 300 seconds to a date and time stored in A1, the formula and result should look like this:
Method 4 – Add any combination of hours, minutes, and seconds to time
=[datetime]+([hours]/24)+([minutes]/24/60)+([seconds]/24/60/60)
The formula above is a combination of the 3 previous formulas. We can divide it into 4 parts, and apart from the [datetime], any of the other three are optional.
For example, to add 3 hours and 15 minutes to a date and time stored in A1, you can apply the following formula and the result should look like this:
Another example would be to add 8 hours and 45 seconds to a date and time stored in A1. You can apply either of the following formulas and arrive at the following result:
As you can see, excluding a certain unit (hour, minute, second) from your operation would require you to exclude its section, or to place a 0 on its corresponding section.
Frequently Asked Question in Adding Time in Google Sheets
Is it possible to subtract time in Google Sheets?
To subtract time in Google Sheets, change the operation(s) inside the formulas from “+” to “-”. An example is changing this formula
“=A1+(5/24)” to “=A1–(5/24)”.
In adding time in Google Sheets, if I add 5 hours to
01/31/2022 9:00PM, will the result be
02/01/2022 2:00AM or 01/31/2022 2:00AM?
The result would be 02/01/2022 2:00AM. The date format 01/31/2022 9:00PM will be recognized by Google Sheets as a datetime value, and as such, it is stored internally as “44592.91667”. Adding 5 (5/24 or 0.2083333333) hours to any datetime value applies the calculation first on the right side of the decimal point. When the value of the decimal point exceeds “1”, that “1” is promptly added to the left of the decimal point, adding 1 to the date.
The calculations would be as follows:
44592.91667 + 0.2083333333 = 44593.125, or the datetime 02/01/2022 2:00AM.
Conclusion on Adding Time in Google Sheets:
First, you need to prepare the date, time, or datetime that you will add in Google Sheets.
To generate a date, time, or datetime in Google Sheets select a cell and press
“ctrl + ;”, “ctrl + shift + ;”, or “ctrl + alt + shift + ;”, for Windows, respectively. Or by selecting a cell and pressing “⌘ + ;”, “⌘ + shift + ;” or “⌘ + alt + shift + ;”, on a macOS device, respectively.
To add any amount of hours to your date, time, or datetime, use the formula:
=[datetime]+([hours]/24)
Substitute [datetime] with the cell address of your date, time, or datetime, and substitute [hours] with the amount of hours you want to add.
To add any amount of minutes to your date, time, or datetime, use the formula:
=[datetime]+([minutes]/24/60)
Substitute [datetime] with the cell address of your date, time, or datetime, and substitute [minutes] with the amount of minutes you want to add.
To add any amount of seconds to your date, time, or datetime, use the formula:
=[datetime]+([seconds]/24/60/60)
Substitute [datetime] with the cell address of your date, time, or datetime, and substitute [seconds] with the amount of seconds that you want to add.
Finally, to add a combination of hours, minutes, and seconds to your date, time, or datetime, use the formula:
=[datetime]+([hours]/24)+([minutes]/24/60)+([seconds]/24/60/60)
Substitute [datetime] with the cell address of your date, time, or datetime, and substitute [hours], [minutes], and [seconds] with the amount of hours, minutes, and seconds that you want to add, respectively.
