by Learn how to calculate time in Google Sheets. You’ll discover everything from finding the duration between two dates to calculating average and total time. You'll also learn how to track hours or minutes between two times, calculate hours worked for payroll, and use advanced tricks to split date and time units.
Now, that we've learnt how to enter dates and time to your spreadsheet, it's time to talk about the ways of calculating time in Google Sheets. We'll discuss the ways of finding time difference in detail, see how to sum dates and time together, and learn to display only date or time units and set them apart completely.
Quick answers to common questions
| Question | Solution | Formula | Format |
|---|---|---|---|
| How to calculate time difference in Google Sheets? | Subtract start time from end time | =B2 - A2 |
Duration |
| How to calculate time between two dates in Google Sheets? | Subtract start date from end date | =B2 - A2 |
Duration |
| How to calculate time past midnight? | Add 1 day if end time is smaller | =(B2-A2+(B2<A2)) |
Duration |
| How to calculate hours worked in Google Sheets? | Subtract start from end and convert time duration to numbers to calculate pay | =(B2 - A2) * 24 |
Number |
| How to calculate total time in Google Sheets? | Add up multiple time values | =SUM(A2:A10) |
Duration |
| How to calculate average time in Google Sheets? | Find the mean duration | =AVERAGE(A2:A10) |
Duration |
| How to add time in Google Sheets? | Add specific units to a time | =A2 + TIME(hours, mins, sec) |
Time / Date Time / Duration |
How to calculate time difference in Google Sheets: elapsed time
When you're working on some projects, it is usually important to control how much time you spend. This is called elapsed time. Google Sheets can help you calculate the time difference in a lot of various ways.
Example 1. Subtract time to get the time duration in Google Sheets
If you have your start time and end time, it's not a problem to find out the time spent:
Let's assume the start time is in column A and the end time is in column B. With a simple subtraction formula in C2, you will find how much time this or that task took:
=B2-A2
The time is formatted as "hh:mm" by default.
To get the results as hours only or as hours, minutes, and seconds, you need to apply a custom format with the corresponding time codes: h and hh:mm:ss. Google even offers a special number format for cases like this - Duration:
Tip. To apply the custom time format, go to Format > Number > Custom number format in your spreadsheet menu.
As you can see in the last row, sometimes your end time can be on a new day. In this case, the simple subtraction formula doesn’t work properly.
To calculate time past midnight in Google Sheets, use this formula:
=B2-A2 + (B2 < A2)
It adds 1 day when the times cross midnight, so you’ll get a proper duration regardless of when the shift ends.
Example 2. Calculate time duration in Google Sheets using the TEXT function
Another trick to calculate the time duration in Google Sheets involves the TEXT function:
=TEXT(B2-A2,"h") - for hours
=TEXT(B2-A2,"h:mm") - for hours and minutes
=TEXT(B2-A2,"h:mm:ss") - for hours, minutes, and seconds
Note. See how the records are aligned to the left? Because the TEXT function always returns the results formatted as a text. This means these values cannot be used for further calculations.
Example 3. Time difference between two dates in hours, minutes, and seconds (Decimal format)
You can track the time spent and get the result in one time unit disregarding other units. This is especially useful for payroll and invoicing, when you need hours as a decimal (e.g., 5.5 instead of 5:30) to multiply by an hourly rate. You can also count not just hours, but only minutes, or only seconds as well.
Note. To ensure correct results, your cells should be formatted either as numbers or automatically: Format > Number > Number or Format > Number > Automatic.
-
To get the number of hours spent, subtract your start time from the end time and multiply the result by 24 (since there are 24 hours in one day):
=(End time - Start time) * 24You will get a time difference as a decimal hours:
If the start time is greater than the end time, the formula will return a negative number, like in C5 in my example.
To calculate time between two dates in Google Sheets when it goes past midnight, use the formula we discussed in Example 1:
=(B2-A2+(B2<A2))*24
Tip. Calculating hours between two times? The INT function will let you see the number of complete hours spent since it rounds numbers down to the nearest integer:
-
To count minutes, subtract the start time from the end time and multiply whatever you get by 1,440 (since there are 1,440 minutes in one day):
=(End time - Start time) * 1440
-
To find out how many seconds passed between two times, the drill is the same: subtract the start time from the end time and multiply the result by 86,400 (the number of seconds in a day):
=(End time - Start time) * 86400
Tip. You can avoid multiplying in all these cases. Just subtract times first, and then apply elapsed time format from Format > Number > Custom date and time. If you click the down arrow to the right of the text field, you'll be able to choose between additional date and time units:
Example 4. Functions to get the time difference in a Google spreadsheet
As always, Google Sheets equips you with three particularly useful functions for this purpose.
Note. These functions work only within 24 hours and 60 minutes and seconds. If the time difference exceeds these limits, the formulas will return errors.
=HOUR(B2-A2)- to return hours only (without minutes and seconds)=MINUTE(B2-A2)- to return minutes only (without hours and seconds)=SECOND(B2-A2)- to return seconds only (without hours and minutes)
How to add and subtract time in Google Sheets: hours, minutes, or seconds
These operations can also be achieved with two techniques: one involves basic math calculations, another - functions. While the first way always works, the second one with functions works only when you add or subtract units less than 24 hours, or 60 minutes, or 60 seconds.
Add or subtract hours in Google Sheets
-
Add less than 24 hours:
=Start time + TIME(N hours, 0, 0)Here's how the formula looks on real data:
=A2+TIME(3,0,0)
-
Add more than 24 hours:
=Start time + (N hours / 24)To add 27 hours to the time in A2, I use this formula:
=A2+(27/24)
- To subtract 24 and more hours, use the formulas above as a basis but change the plus sign (+) to the minus sign (-). Here's what I've got:
=A2-TIME(3,0,0)- to subtract 3 hours=A2-(27/24)- to subtract 27 hours
Add or subtract minutes in Google Sheets
The principle of manipulating minutes is the same as with the hours.
-
There's the TIME function that adds and subtracts up to 60 minutes:
=Start time + TIME(0, N minutes, 0)If you are to add 40 minutes, you can do it like this:
=A2+TIME(0,40,0)If you are to subtract 40 minutes, here's the formula to use:
=A2-TIME(0,40,0)
-
And there's a formula based on simple arithmetic to add and subtract over 60 minutes:
=Start time + (N minutes / 1440)Thus, here's how you add 120 minutes:
=A2+(120/1440)Put the minus instead of plus to subtract 120 minutes:
=A2-(120/1440)
Add or subtract seconds in Google Sheets
Seconds in Google Sheets are calculated in the same manner as hours and minutes.
-
You can use the TIME function to add or subtract up to 60 seconds:
=Start time + TIME(0, 0, N seconds)For example, add 30 seconds:
=A2+TIME(0,0,30)Or subtract 30 seconds:
=A2-TIME(0,0,30) -
To calculate over 60 seconds, use simple maths:
=Start time + (N seconds / 86400)Add 700 seconds:
=A2+(700/86400)Or subtract 700 seconds:
=A2-(700/86400)
How to sum time in Google Sheets (Total time)
To find the total time in your table in Google Sheets, you can use the SUM function. The trick here is to choose the correct format to display the result.
By default, the result will be formatted as Duration - hh:mm:ss
But most often the default time or duration format won't be enough, and you will need to come up with your own one.
A7:A9 cells contain the same time value. They are just displayed differently. And you can actually perform calculations with them: subtract, sum, convert to decimal, etc.
How to calculate average time in Google Sheets
To find the average time in your table, you can use the standard AVERAGE formula:
=AVERAGE(B2:B4)
Same as with the SUM function, the trick here is picking the right format for the result cell. If you need the average time duration, go to Format > Number > Duration.
How to calculate hours worked in Google Sheets
Calculating payroll hours isn't as simple as just subtracting start time from end time. A proper timesheet also needs to account for breaks and overtime.
To count hours worked you need to subtract the break from the whole shift:
Let's say you've got start time in column A, end time in column B. Then you put break start in C and break end in D. Your formula would look like this:
=(B2-A2)-(D2-C2)
If you or your employees work night shifts, you need to add a condition to check if it’s still the same day:
=(B2-A2+(B2<A2)) - (D2-C2+(D2<C2))
Note. Make sure that your column with total work time is formatted as Duration to correctly display the results.
In many organizations lunch time is fixed. In this case you can use the subtract time formula from the previous chapter.
For example, this formula works for a 30-minute break:
And this formula for an hour break:
Note. If you have any night shifts, add the condition in the formula =(B2 - A2 + (B2 < A2)) - TIME(1, 0, 0).
Regular hours and overtime usually have different pay rates, so you need to calculate them separately.
To calculate overtime, you can use the IF function:
This formula checks if the total work time is more than 8 hours. If yes, it subtracts 8 and shows the rest. If not, there will be a zero.
=IF(E4 > TIME(8, 0, 0), E4 - TIME(8, 0, 0), 0)
Once you've got your total and overtime hours, subtract the overtime from the total work time in a separate column to get your regular hours.
But as we discussed before, you must convert the time into decimal format first to use it in payroll. Just multiply the results in your columns for overtime and regular hours by 24, then go to Format > Number > Number:
Now you're ready to calculate pay!
Note. Need to calculate the total number of workdays for your payroll period? Learn how to use the DATEDIF and NETWORKDAYS functions to calculate the number of days in Google Sheets.
Extract date and time from a full "date-time" record
Let's imagine that one cell in Google Sheets contains both, date and time. You want to set them apart: extract only the date to one cell and only time to another.
Split Date time using Number format
In order to display date or time in one cell on your screen or to print it, just select the original cell, go to Format > Number and choose Date or Time.
However, if you'd like to use these values for future calculations (subtract, sum, etc.), this won't be enough. If you don't see the time unit in a cell, it doesn't necessarily mean that it's absent, and vice versa.
So what do you do?
Split Date time using formulas
Google stores dates and time as numbers. For example, it sees the date 8/24/2017 11:40:03 as the number 42971,4861458. The integer part represents the date, the fractional - time. So, your task is down to separating integer from fractional.
- To extract date (integer part), use the ROUNDDOWN function in cell B2:
=ROUNDDOWN(A2,0)
The formula rounds the value down and casts the fractional part away.
-
To extract time, place the following subtraction formula into C2:
=A2-B2 -
Copy the results into the third row and apply Date format to B3 and Time format to C3:
Use the Split Date & Time add-on
You may be surprised but there's one special add-on for this job. It's really small and easy but its contribution to Google Sheets cannot be overstated.
Split Date & Time splits all Date time records in your entire column at once. You control the desired outcome with just 4 simple settings:
You tell the add-on:
- Whether there's a header row.
- If you want to get the Date unit.
- If you want to get the Time unit.
- And if you'd like to replace your original column with the new data.
It literally takes the burden of splitting date and time units off your shoulders:
Split Date & Time is part of the Power Tools collection so you will have more than 40 other useful tools for Google Sheets at hand.
These are the ways to not only display date or time, but to separate them to different cells. And you can perform various calculations with these records now.
I hope these examples will help you solve your tasks when working with dates and time in Google Sheets.
Spreadsheet with formula examples
Calculating time in Google Sheets (make yourself a copy to practice)
Latest comments
I implemented a timesheet to track my hours over several months. The day-to-day calculations are working great and I get them looking like 08:00 for 8 hours and 12:45 for a 12 hour and 45 minute period. I then add each of the days together and get a weekly total that has the same format, but all in hours so currently 135 hours and 40 minutes, or 135:40. This is a Duration format. If I make it a strict hh:mm field, it comes out as 15:40, hiding the 4 "days" time. But since it is a Duration, I'm told it needs to be multiplied by 24, which makes no sense to me since I already have the hours; I don't need to convert from days to hours. How can I extract the hours value and the minutes value separately so I can perform an additional calculation on them?
As I understand, you’d like to extract the number of days from your duration. The easiest way to do this is to add a new column next to your duration values and use this formula:
=ROUNDDOWN(| ) |
This will give you the number of full days in the duration (without hours or minutes). Keep your original duration column formatted as hh:mm, and your results should display exactly as you need.
Hello,
I am trying to keep track of "Door(arrival time) to Activation time" in minutes. Sometimes the activation time is minutes before the arrival/Door time. When this happens, I receive a negative number (obviously). What is the formula to achieve the difference between the two times? And then when I receive this negative number, is there a way to have it read as "0"?
I currently have the Page time, the Arrival Time and my "Door to Activation time" columns in the TIME Format so when I attempt the simple =J37-H37 formula I receive results in a decimal. I need the decimals to populate as whole numbers and I need the negative result to populate as "0".
For instance, EMS crew pages the ER to notify them of an incoming patient at 07:50 AM. The patient arrives to the ER at 08:00 AM. What I am trying to calculate is the page(activation) time 07:50 AM minus the Arrival time 08:00 AM = - 0.01. I need this decimal to result as a whole "0".
Alternatively, 08:00 AM - 07:50 AM= 0.01. I need this to translate to 10 (minutes)
Any and all help will be greatly appreciated! My brain hurt even typing this out.
Hello Holland,
For 0.01 to appear as 10 minutes, make sure to format the resulting cell as Duration or one of the custom formats as shown in this part of the article.
For -0.01 to appear as 0, you need to incorporate IF function to your calculations.
how to add current time in google sheet
Hello vignesh,
You will learn it in this article: Google Sheets date and time functions
Hello, I am wanting to create a time sheet for some fellow employees but I cannot find a formula that shows how to get the total hours worked from doing the clock in, out, in, out for their lunch breaks (example: in 6:45 am, out 1:20 pm, in 2:05 pm, out 5:20 pm = 9 hours and 50 minutes worked) I cannot get it to equal the time total worked with any formula I have tried. Please let me know if there is a way to do that.
Hello samantha,
Just calculate the time difference between the start time and end time as shown in this part of the article and then subtract the lunch:
=D2-A2-(C2-B2)
Hi there,
I'm trying to efficiently figure out my employees hours worked and pay for those hours. So I have figured out how to subtract time started from time ended and then how do I translate that time- back to hours so I can multiply that by their hourly pay. For example:
Employee 1: Clock In- 16:15 Clock Out- 21:30 and get 5:15 then I try to multiply this number by $18/hr and am getting a wacky number. any advice??
Hi Beth,
Try to multiply your time difference by 24 to get the result as a decimal first. Then it will multiply by an hourly rate correctly. Please see this section for formula examples: Time difference in hours, minutes, and seconds
Natalia, firstly you are a wizard when it comes to this! Thanks for your help in advance!
Here's the layout of my timesheet (that gets fed from the form)
Timestamp Name Email Your Position Date Completed Time Started Time Completed Hours worked
2/6/2024 17:06:06 sample sample 2/5/2024 3:45:00 PM 5:00:00 PM 1.15
I'm trying to have the sheet calculate the hours worked working with the AM PM time - is that possible?
Appreciate your lovely words, Avi!
For your task, you need to subtract time started from time completed (and multiply it by 24 if you'd like to get the result as a decimal). See this section for formulas examples: Time difference in hours, minutes, and seconds
Hi Natalia, I am building a google sheets that allow me to arrange schedule for employees.
After we key in time in and time out, I already make it to be able to auto calculation for us to count the total working hours. But besides that, I need your help on this:
If the working hours is more than 7.5hours, need to minus one hour break time.
If the working hours is less than 7.5hours, no need to minus any break time.
How do I do that? Kindly assist.
Thank you so much.
Hello KY,
The IF function will help you check if the working hours are more/less than 7.5 hours and add or subtract the required amount of time correspondingly.
Dear all
I would like to make a schedule for production tasks in a weekly production plan. So for every product there is number of working hours
A-4h
B-15h
C-22h.....
and then I would like to calculate if I work from Monday 8am, and shift is 8 hours when will every product be finished?
Hello Milos,
Though I can't create a schedule for you, I can suggest functions that may help you with the task:
Hope these help!
Hi Natalia,
I enjoyed reading your blogs. May I ask your help if this is possible:
Is there a way to to get the sum of hours in google sheet per cell if I the value per cell is not a number but a "text" and that the assumption is each cell with a "text" value equates to 15 mins. Does that make sense?
Thanks!
Hi Rico,
Thank you for your feedback!
Since Google Sheets can't calculate text, I believe you'll still need some helper table showing the time for each text so you refer to it in your formulas. But I'll be able to suggest better if you provide a few examples of the data you have, how you want to calculate it and what should be as a result.
Tracking Number of Minutes Late from Scheduled Time on Sheets
Hello Ravneet,
I believe this simple subtraction will do the trick for you.
Hi Natalie,
I have a sign-in sheet that when you select your name from a drop down menu the computer returns the time you signed in. I want a formula that calculates how many minutes you sign in late beyond 7:30. Also I want any time signed in beyond 7:30 to turn red.
Many thanks.
Hi Hayden,
To calculate minutes, you need to subtract 7:30 from the actual sign in time. You'll find formula examples here.
To color cells red as well, you need to use conditional formatting.
Hi Natalia,
I am subtracting the hours accumulated for vacation time remaining. I'm trying to figure out how many days left in my vacation time.
If I have 72 vacation hours (H3) and have used 3 hours (H4), 3 hours (H5) and 24 hours (H6) and now have 42 hours (H11=H3-H4-H5-H6-H7-H9-H10) remaining, how do I calculate or how do I format cell H12 to reflect the remaining days left if I work 3.5 days/ week (7.5 hours each)?
Hope this is clear.
Thank you!
Hi fabio,
If I understand your task correctly, you can use this function:
=(H11*24)/7.5
Multiply H11 by 24 to return hours as a decimal first. Then divide it by the number of hours you work daily. I've got 5.60 days.
If this is not exactly what you need, please specify what the result should be and elaborate on the last part of your task: "reflect the remaining days left if I work 3.5 days/ week (7.5 hours each)"
Hi Natalia,
I am currently tracking my task durations in google sheets, which I then add up (or subtract) to calculate the total time duration in the same column.
I am currently using the format : Elapsed hours (1) h: Minute (1) m
The end result is as expected, for e.g : 0h:30m , 5h:0m etc
Is there a way to hide the hours (h) or minutes (m) if they are 0?
So from the example above, I'd like it to display as 30m and 5h only.
What would be the right format to use to achieve this?
Hi Drake,
If you set up the format using the Google Sheets number format, it's going to show minutes/hours even if they're zero units.
To hide zero values, I believe you need to incorporate the IF function & check if the result has 0 minutes or 0 hours. Then return the result in the required format set up by the TEXT function in the formula itself.
I have a self-made tool to keep track of my work hours, now I want to have it calculate the number of hours over 40.
I'm using the Time hours:min:sec format for the time cells.
Hello Tim,
I described how to add more than 24 hours in this part of the blog post.
Hello,
Is there a formula for adding time im a single cell? So if i had 9-5 in a cell, could a formula add the 9-5 to equal 8 in another cell? Im tying to make a schedule using this format oppose to the duration method.
Thanks in advance.
Hello Jose,
You can try using the IF function: make it return 8 if another cell contains 9-5.
Hi I was wondering if there is a formula that can calculate the number of minutes after a specific time. I need total number of minutes after 3pm. Thank you!
Hi Kim,
Assuming your start time (3pm) is in A2, here's the formula to try:
=HOUR(NOW()-(TODAY()+A2))*60 + MINUTE(NOW()-(TODAY()+A2)) + SECOND(NOW()-(TODAY()+A2))/60
Make sure to format a cell with this formula as a number.
Hi! My hope is that there is a sum formula for adding song lengths that contain hours, minutes, and seconds. I have them set up in this format: 01:04:35 but it doesn't add it as hours, minutes, and seconds... Is there a formula? Do I need to put each quantity in separate cells? hours in one, minutes in one, and seconds in another? thanks!!!!!
Hi Aimée,
If all of them are in the Duration format (01:04:35), you should be able to sum them correctly with a simple SUM function, please see the examples in this part of the article.
Is there a way when subtracting time to automatically convert time format of minutes to hours as my goal is like this:
On A1 is start time say 8:00 AM and on A2 is end time as 8:40AM, and it returns the duration on A3 as "40 mins".
What I aim is that if it's more than 60 mins, say A1 is 8:00AM and A2 is 9:20AM, it automatically formats A3 result to "1 hr 20 mins"
Hello John,
Try this formula:
=IF(MINUTE(A2-A1)>60, (HOUR(A2-A1)&" hours, "&MINUTE(A2-A1)&" minutes"), A2-A1)
We need formula for if working hours is 6 and 1 person working 6.5 hours and we need to show he worked 0.5 hour extra (fixed time 10:00AM to 04:00PM) (Work time 10:00 AM to 4:30:00PM) actual extra work 0.5 hour
what is the formula for that
Hello Balajinaik,
You need to use the IF function for such calculations:
=IF((B2-A2)*24>6,(B2-A2-TIME(6,0,0))*24,"")
I typed a start time colum and an end time colum via the 13:25 format (the date is on a separate colum): I don't understand how to calculate the subtraction : meaning the differnce between them.
How can I do that please?
Hello Sharon,
You need to subtract end time from start time. Please see the examples here:
Example 1. Subtract time to get the time duration in Google Sheets