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.
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.
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:
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
You can track the time spent and get the result in one time unit disregarding other units. For example, count the number of only hours, only minutes, or only seconds.
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):
You will get a time difference as a decimal:
If the start time is greater than the end time, the formula will return a negative number, like in C5 in my example.
To count minutes, substitute the start time from the end time and multiply whatever you get by 1,440 (since there are 1,440 minutes in one day):
To find out how many seconds passed between two times, the drill is the same: substitute the start time from the end time and multiply the result by 86,400 (the number of seconds in a day):
As always, Google Sheets equips you with three particularly useful functions for this purpose.
=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)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 less than 24 hours:
Here's how the formula looks on real data:
=A2+TIME(3,0,0)
Add more than 24 hours:
To add 27 hours to the time in A2, I use this formula:
=A2+(27/24)
=A2-TIME(3,0,0)
- to subtract 3 hours
=A2-(27/24)
- to subtract 27 hours
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:
If you are to add 40 minutes, you can do it like this:
=A2+TIME(0,40,0)
If you are to subtract 20 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:
Thus, here's how you add 120 minutes:
=A2+(120/1440)
Put the minus instead of plus to subtract 120 minutes:
=A2-(120/1440)
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:
For example, add 30 seconds:
=A2+TIME(0,0,30)
Or substitute 30 seconds:
=A2-TIME(0,0,30)
To calculate over 60 seconds, use simple maths:
Add 700 seconds:
=A2+(700/86400)
Or substitute 700 seconds:
=A2-(700/86400)
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.
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.
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?
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.
=ROUNDDOWN(A2,0)
The formula rounds the value down and casts the fractional part away.
=A2=B2
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.
63 Responses to "Calculating time in Google Sheets: subtract, sum and extract date and time units"
Thank you! This just saved me so much time!
Get it? Time? Like this article?
Seeya guys, gonna off myself.
HI, When the "duration, h" column is added to get a total hours the formula SUM(E3:E100) returns less than 24 hours. It restarts the summation each time it hits 24 hours. So if the total hours worked is 53 hours and 45 minutes, the return is 5:45 which is incorrect. How can I correct that?
G:
How is the cell duration h formatted?
The issue may be one of formatting the cell the way you want it to display.
Hey!
Thanks for a super interesting topic. I'm trying to build a countdown displaying Days, Hours and Minutes and yours is the only subject I can find on the matter.
Though I have some major problems getting it to work correctly and was wondering: Would you mind sharing your Sheet for us slow people to have a poke at?
Thanks in advance and thanks for a super post!
//Ola
Hi, I'm trying to sort out how to sum time for a time trial event on a racing game. There are several weeks being added together for a total combined time of all events. The issue I'm having is Sheets is rounding the time up. I need it to show exact times, so if a lap time is 1 minute 33 seconds and .742 so, 0:01:33.742, I need it to show that time in the cell, and to represent that time in the total time cell, which I've used the sum function for. Drivers' times are often too close for them to be rounded up.
Any help appreciated! Thanks!
Dave:
Have you tried to use this in the Custom section of the Format Cells? mm:ss.000
Looking to set a series of times based upon a single "operator entered START time" and about 10 subsequent times where I know the number of minutes between each step.
It would be quite possible these would go over midnight to the next day.
Example: Start time = 11:00 AM
Step 1 [15 min] = 11:15 AM
Step 2 [20 min] = 11:35 AM
Step 3 [4 hours] = 3:35 PM
Thanks!
I'm looking for the same!
I'd like input the event start time and have it populate the cells with a list of times other coordinated events will be happening
Hi thank you for the above time calculation.
There is one more help I need about the above formula. Is there a way to know the time spent on a particular task submitted per employee.
Example : An employee used google form to submit a completed task. That showed 8/10/2018 6:30:00 timestamp, then claimed a new task which ended at 8/10/2018 7:15:00, then claimed another task which ended at 8/10/2018 7:35:00. Employees let’s say are required to complete 3 task per hour. In a situation where by one has more than hundreds of people using the form to submit their task completion per hour in one google tracker . What is the easiest way to calculate the hr spent by each employee and the completed task per hour. 2 is there a way to add a formula on a separate column that auto calculate hours spent by each employee when 3task is completed. ? The logic here is I would like to know the most product employee in an hour thanks.
Halima’s slept at 2200h if she woke up at 0600h for how long had she slept.
Hi
These are probably dumb questions but here goes....
How would I calculate time elapsed by days on a project I'm tracking.
Ex: Task 1 is supposed to start 1/23/18 but it did not start until 1/26/18
I also am using conditional formatting in a status column.
Is it possible to just have Task column, Start date column, Days Delayed column and Status column.
Hi Lauren,
You can calculate the time difference between dates and have it returned in days:
=DATEDIF(A2,B2,"D")
As for your second question, could you please specify what you mean in detail?
Hi,
I'm trying to create a sheet for hours worked with start and stop times. They way we usually calculate it, we enter the hours worked first, and have to add in the start and stop times later. If the employees always start and take lunch at the same time, how could I make a formula that will auto-populate the start and stop times for the amount of hours worked for a given day?
Ex: Start Stop Start Stop Hours
7:30 AM 12:00 PM 12:30 PM 4:30 PM 8
I would like to just be able to enter in "8" in the hours column, and have the start & stop times be calculated using a formula. Thank you!
I noticed that when changing Duration to hours, day, etc... the Hours are always one day off. I notice this on your and on mine as well. Example: above you had 81:01:40 and it displayed 2 days, 1 hour, 40 minutes. It should be 3 days, 1 hour, 40 minutes.
Any idea why it automatically drops a day off?
thanks
Hi Robert,
Thank you for pointing that out.
We'll update this article with more correct calculations soon.
Newbie here! This is the formula I am using in a Google sheet to determine the time elapsed between opening a job ticket and completion. How do I make it account for work days? It's currently adding 2 days for jobs started Friday but completed on Monday.
=CONCATENATE(TEXT(FLOOR(L753-F753,1),"@")," Days",TEXT(L753-F753," h:mm"))
Hi Patty,
We've added NETWORKDAYS to your formula - this function is used to count the number of working days between two dates. As a result, we've got this:
=CONCATENATE(TEXT(NETWORKDAYS(F753,L753)-1,"@"),if((NETWORKDAYS(F753,L753)-1)=1," Day"," Days"),TEXT(L753-F753," h:mm"))
How to calculate time only between working hours
Example employee started a task 2019-05-23 13:00 and finished 2019-05-28 10:00 it says 117hours but we are only open from 9 to 6 monday to friday is there way to only calculated hours between those time ?
Thanks !
Cedrik,
Let's assume your data looks the following way:
Where the opening time is in B1, closing time - in B2, the start task time is in B5, end task time in C5.
Here's the formula that should work:
=(NETWORKDAYS(B5,C5)-2)*(B2-B1)+(B2-MOD(B5,1))+(MOD(C5,1)-B1)
Is there a way to calculate the elapsed time for 2 different dates and times with a format of hours and minutes (hh:mm)?
Katrina,
Fist, subtract the start time from the end time. Then, select the results and go to Format > Number > More formats > More date and time formats. Set and apply the following:
Elapsed hours:Minute
Hope this helps.
I'm trying to find a formula that converts duration in hr:min:sec format (e.g. 00:04:15) to total seconds in number format (255).
Any help greatly appreciated.
Hello Peter,
Please try the following:
=HOUR(A1)*3600+MINUTE(A1)*60+SECOND(A1)
how do i get the the total seconds for two cells. example start time is 6:44:56 am and end time 6:48:30 am?
Hello Bryan,
If you need to have both start and end time in seconds, use the formula from the comment you replied to. Just remember to use your own cell references.
If you need the time difference in seconds, first, find it by the example of the first paragraph, then apply the formula from the comment you replied to.
Hi there!
Thank you so much for this helpful information.
Is it possible to calculate a sum of the total time formulas?
e.g.: (=TEXT($F3-$E3,"hh:mm")) + (=TEXT($F4-$E4,"hh:mm")) = (=SUM(G3:G33))
I have been trying the above to reach a sum but to no avail.
Thank you!
Lauren
Hi Lauren,
If I got your formula right, the TEXT function returns records as text and they cannot be used for calculations as is. You could try using VALUE in order to turn Text cells back to Time like this:
VALUE(TEXT((F3-E3),"HH:MM"))
but VALUE doesn't take ranges, so you'd have to do that for each cell within your range - G3:G33 - in order to create a formula:
=SUM(VALUE(TEXT((F3-E3),"HH:MM")),VALUE(TEXT((F4-E4),"HH:MM")),VALUE(...),...)
Hi,
I've built a Google Sheet to track service/repair work on construction equipment by our shop technicians.
There are several steps in the operation process - 1) equipment arrives, 2) diagnostics, 3) labor quote by service dept., 4) parts quote by service, 5) full-quote sent to customer, 6) customer response, etc. We are date/time stamping each step of the process using this format (6/19/2019 0:00:00).
I built a column to calculate the duration between each step to help identify the stages of the process where we have the biggest opportunity to gain efficiencies (time). A sample formula I used for the calculation is - =ArrayFormula(IFERROR(if(Len(F3:F),(int(H3:H-F3:F)&"d "&text(H3:H-F3:F-int(H3:H-F3:F),"HH:MM:SS")),)))
While the duration formula above works great, I found that when I attempted to add an additional column of a target time frame (i.e. - 6 hours to complete for example); I could not use a comparative formula ( =if(I3>=J3,"X","√") ) because the duration calculation is in Text format.
Any thoughts? I'm trying to highlight work orders (conditional formatting) that are outside of target deadlines, so that managers spend more time focusing on the "jobs" that are underperforming.
Thanks!
Hi Kurt,
Since you use the TEXT function in your formula and such textual parts as &"d "&, the result will be turned into a text string. I'm afraid there's no way to make Google understand it as duration and compare with other time. I can only suggest you build a formula without using text parts in it.
Can I ask how can you convert 3 mins 51 secs to 00:03:51 format? Thanks.
If your cell states "3 mins 51 secs", you won't be able to convert it. Since it contains text, Google will interpret it as a string.
But if you have 3:51 in a cell, custom number formats will do the trick. You can read more about them in this blog post.
Thanks.
I've been trying to SUMIF the duration but always ends up with 0. After playing around I've found that because the data (Name and duration only, no start or end time) was in XX:XX:XX:XX format it doesn't work. If i remove the first 2 leading zeros, that's the only time the SUMIF or even SUM function would work. Any idea how to make this work?
Mac,
the duration format for Google Sheets is:
elapsed hours : elapsed minutes : elapsed seconds
What should another pair of numbers represent? Milliseconds?
Please go to Format > Number > More Formats > More date and time formats, and make sure you've set the correct format for your durations.
Hey Natalia,
Thanks for taking the time to respond. The first 2 zeros are always just 00 but I'm guessing they're days? it is suppose to be hh:mm:ss. The data itself is copied from another source and even if I tried all sorts of format, it stays the same.
Hey Mac,
Thank you for the details.
Unfortunately, Google Sheets doesn't recognize values formatted as XX:XX:XX:XX as any types of dates. It automatically aligns them to the left and treats as text strings.
You need to remove those first zeros, and the records will then be automatically turned into Duration.
Btw, we have a tool that can help you remove chars by position quickly. It is free for 30 days and you can use it on your data and remove the first 3 chars (00:).
Hello,
I am having a problem adding and subtracting hour duration. Addition seems to be working correctly (6:30+2:00=8:30 8 hours 30 minutes). But I would like to subtract the total from 40 hours (40:00-8:30=31:30). Instead sheets is converting hours to days (when putting (40-31:30) in the cell it computes to 960:00-31:30=928:30). If I enter 40:00 - 31:30 I receive an error.
Hello Katie,
When you enter 40-31:30, Google Sheets treats those as numbers and can't count.
I'd advise you to put your durations in separate cells; then apply the duration format to them (Format > Number > Duration); and then use cell references to count =A2-B2. Everything will then work flawlessly.
Hi there.
I have used the formula for "Add or subtract minutes" in two different columns. One with
"G3-time(0;Q2;0)" and one with B3+time(0;$P$2;0) It works great with a positive number in Q2 and P2.
But it would like to have the possibility to and a negative number hence the range I need to enter goes from -60 - +60.
I can figure that out?
Hi there, Michael,
You need to incorporate the IF function for the task:
=IF(Q2<0,G3-TIME(0,ABS(Q2),0),G3+TIME(0,Q2,0))
and
=IF(P2<0,B3-TIME(0,ABS(P2),0),B3+TIME(0,P2,0))
Thank you so much Natalia.
That help me a lot and did the trick. Sorry for the long "Thanks" response. I thought I'd recived a e-mail when someone replied my question. Again thank you.
I am new to google spreadsheet, what is the formula for checkbox = show current time
Ex: check checkbox on A2 then current time will appear on B2
Thaks so much
Welcome to the fold, Fred :)
The IF function will help you out:
https://www.ablebits.com/office-addins-blog/2017/05/16/if-function-google-sheets/
Checkboxes represent TRUE/FALSE, so the following should do:
=IF(A2=TRUE,NOW(),"")
Hi, I'm having a weird problem, and I can only find answers that is seems no one would ever want lol.
I am using =SUM(E9-D9) to calculate elapsed time on an employee timesheet. The problem I have is I need the answer to be in a decimal format for payroll and I can't find a way of doing it without adding another cell with the conversion.
9:30 - 1:45, with this formula I get the answer 4:15. I need the answer to say 4.25. Any ideas?
Hi Madden,
If I get it correctly and you use =SUM(E9-D9) to calculate the elapsed time (though it does look strange), try this formula to do both calculate and convert to decimal:
=HOUR(SUM(E9-D9))+(MINUTE(SUM(E9-D9))/60)
Hi Natalia,
I'm building a spreadsheet to audit the release times of tour buses. I have a cell (A1) that lists the cost of a bus per hour. I have one cell (B1) for the time a driver records the bus released, and another cell (C1) for the time a tour guide records the bus released. Finally, I have a cell for the results (D1)
First, I need to figure out the difference in reported times. If the difference is less than 15 minutes in either direction (negative or positive), I'd like D1 to return "$0.00." If the difference is more than 15 minutes, I'd like D1 to return the total difference in time multiplied by the cost of the bus (A1). In order to get these returns, I think the differences in time need to be represented as a decimals instead of time formats, but I'm not sure if that's right or how to do that. Any advice would help. Thank you so much!
Hi Adam,
Thank you very much for the clear description of your task. :)
Please try the formula below on your data:
=IF(ABS(C1-B1)<TIME(0,15,0),"$0.00",(MINUTE(ABS(C1-B1))+HOUR(ABS(C1-B1))*60+SECOND(ABS(C1-B1))/60)*A1)
Hi
I am just trying to create a staff rota. start time/finish time/total hours worked. For some reason some of the hours are adding correctly like 08.30 - 17:00 is 8.5 hours but 09:30 - 15:00 is coming up at 9 hours? Should my total column be in a different format? I also want to minus 30 minutes from the break, can I do this. Sorry for the really basic question
Hi Donna,
Could you please provide the formula you use and specify the format of your source and result cells?
Hi Natalia,
Im struggling to correctly set up a sleep tracking spreadsheet. My end result is to compare weekly sleep durations but the issue comes with the following example
Day 1: Time asleep 1AM -> Wake up 7AM => Duration 6h [easy]
Day 2: Time asleep 11PM -> Wake up 7AM => Duration -+ 16h
Obviously if I do manually reverse the subtraction I can get it but I'm trying to set it up automatically as I'm using this =ARRAYFORMULA(IF(C3:C370, AY3:AY37 - C3:C37, "")) which does get me the daily correct Duration but when doing the weekly average its incorrect as the numerical values of those dates that I fell asleep before 00:00 is wrong.
Any ideas on how to go about this?
Hi Ivan,
If you enter time without a date, Google Sheets treats these times as of belonging to one and the same day. You can check that by changing the format to Date time.
So I'd advise you to enter both date and time to your cells (just set the format as Time to keep the date hidden) and you should get the correct results.
Hi Natalia,
Thanks for all the help you are giving! My question is, I am trying to average a group of numbers that are minutes and seconds, and I can't figure out the formula to use. as follows;
:20
1:02
1:07
:52
:47
:52
Thank you!
Hello Rachel,
First of all, you need to enter seconds along with minutes -- 0:20, 0:52, 0:47, etc -- for Google Sheets to understand the contents as time.
Then, use the AVERAGE function.
Great thread so far, thanks. Is there a way to just use one cell to calculate duration? ie.
05:00 - 15:00
05:30 - 14:00
=Total # of hours
Thanks,
Mike
Thank you for the feedback, Mike.
For your task, you'll need to wrap all formulas used to find the time difference with one SUM function, like this:
=SUM(B1-A1,B2-A2)
Make sure to format the cell with the formula as Duration, it should work then.
Hi Natilia,
I've been beating my head against this one today. I have two columns that are recording dates with the calendar function in data validation, how can I calculate how many days have elapsed between the two columns.
Column A records the first date, and column J records teh second date, and I want to be able to calculate teh total amount of days.
Thank you
Hi Jeff,
What if you try this formula:
=ARRAYFORMULA(DATEDIF(A2:A20,J2:J20,"D"))
It's enough to enter the formula to the first cell -- it will expand automatically to all rows you specify in the range thanks to Arrayformula.
Hello,
What is the formula if I need to display the duration in Hours/Min/sec when all I have is the seconds?
1935.4
1974.5
4140.8
1119
774
600.1
Thank you
Hello Thaylin,
Do you calculate these numbers or enter manually? Also, are the numbers after the dots milliseconds?
Hello,
These Numbers are captured from an app that generates them, I then have to paste them into Sheets. the decimal is milliseconds.
Thank You
Thank you for the details, Thaylin.
First, you need to make Google Sheets understand that those are seconds. For that, divide each number to 86400 (the number of seconds in a day). Supposing your time is in column A, use formulas like this:
=A2/86400
Then select cells with formulas and apply the Duration number format. To see milliseconds as well, you'll need to proceed to More date and time formats, and add them to the format manually. Please check the tip at the very end of the 3rd example in this paragraph to get a better idea of that.
Thank You for the help.