Google Sheets offers several date and time functions, but when it comes to adding hours, minutes, or seconds to a time or date value, you don’t need any complex function. A few simple formulas will do the trick.
This post contains quick and easy methods to add hours, minutes, or seconds to a time or timestamp in Google Sheets.
Quick Summary: How to Add Hours, Minutes, or Seconds in Google Sheets
To add time in Google Sheets, use formulas that divide by the number of units in a day:
- Add hours:
=A1 + B1/24 - Add minutes:
=A1 + B1/1440 - Add seconds:
=A1 + B1/86400
Format results as Time or Date time from the Format menu.
Add Hours, Minutes, or Seconds to Time in Google Sheets
Let’s begin with an example.
Note: In addition to the formulas, you may need to apply number formatting to the output cells. I’ll mention formatting tips wherever necessary.
1. How to Add Hours to Time in Google Sheets
Assume:
- Cell A2 contains the time
10:25:00 - Cell B2 contains the number
2(hours to add)
The format in A2 can be either 24-hour or 12-hour; the formula works regardless.
Formula #1
=A2 + B2/24This adds 2 hours to 10:25:00 and returns 12:25:00.
Formula #2: Apply to Entire Columns
To add hours across a column of values:
=ArrayFormula(IF(LEN(A2:A), A2:A + B2:B/24, ))Formatting the Output
- Select the output range (e.g., C2:C)
- Go to Format > Number > Time
Add Hours to a Timestamp (Date + Time)
Adding hours to a timestamp works the same way. But use Date Time formatting for clarity.
Example:
- Cell A2:
23/03/2019 23:00:00 - Cell B2: 3
- Formula (in C2):
=ArrayFormula(IF(LEN(A2:A), A2:A + B2:B/24, )) - Format the output column as Date Time:
Format > Number > Date Time
Result: 24/03/2019 02:00:00
This shows that the completion time falls on the next day.
2. How to Add Minutes to Time in Google Sheets
To add minutes, divide the number of minutes by 1440 (since 1 day = 1440 minutes).
Example:
- A2:
16:30:00 - B2:
150(minutes)
Formula #3
=A2 + B2/1440Formula #4: Array Version
=ArrayFormula(IF(LEN(A2:A), A2:A + B2:B/1440, ))
Formatting
- For time values: Format > Number > Time
- For timestamps: Format > Number > Date Time
3. How to Add Seconds to Time in Google Sheets
To add seconds, divide by 86400 (1 day = 86400 seconds). It follows the same pattern as the examples above.
Example:
- A2:
12:00:00 - B2:
90(seconds)
Formula:
=A2 + B2/86400Or for arrays:
=ArrayFormula(IF(LEN(A2:A), A2:A + B2:B/86400, ))Format the output depending on whether you’re working with time or timestamp values.
What if the Seconds Contain Milliseconds in Cell B2?
No changes are needed in the formula. If B2 contains a decimal like 90.15 (90 seconds and 150 milliseconds), it will still work.
To display milliseconds in the result:
- Select the result cell.
- Go to Format > Number > Custom number format.
- Enter:
hh:mm:ss.000 - Click Apply.
This will show the output with milliseconds, like 12:01:30.150.
Summary Table
| Task | Formula | Divide by |
| Add Hours | =Time + Hours / 24 | 24 |
| Add Minutes | =Time + Minutes / 1440 | 1440 |
| Add Seconds | =Time + Seconds / 86400 | 86400 |
Additional Resources
Explore more time-related tutorials:
- Countdown Timers in Google Sheets (Easy Way!)
- How to Check Time Input in a Range of Cells in Google Sheets
- Elapsed Days and Time Between Two Dates in Google Sheets
- How to Convert Military Time in Google Sheets
- How to Use COUNTIFS with Time Ranges in Google Sheets
- How to Automate Overtime Calculation in Google Sheets
- Formatting Date, Time, and Numbers in Google Sheets Query
- How to Convert Seconds to HH:MM:SS Format in Google Sheets
Hi Prashanth,
Thank you for this information.
I was wondering if you know of a way to automatically increment the time in a column that holds a date and time by adding 1 hour.
Autofill doesn’t seem to work for this. Would you happen to know how I can accomplish this?
Thank you in advance,
David
Hi, David,
Here is my latest article on this.
How to Increment DateTime by One Hour in Google Sheets (Array Formula). Sample Sheet included.
Best,