by 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.
How to calculate time difference in Google Sheets
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.
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 in 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.
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:
If the start time is greater than the end time, the formula will return a negative number, like in C5 in my example.
Tip. 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 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:
=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
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.
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:
The add-on is part of the Power Tools collection so you will have more than 40 other useful add-ons 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)
393 comments
what if i want to find the average of time with milliseconds, for example,
1:23.45
5:67.89
Hello David,
If the AVERAGE function returns errors, the problem is in the format of your source data. Google Sheets doesn't recognize the numbers you sent as numbers/time/duration. Also, the record 5:67.89 can't have 67 as seconds, the max can be 59.
So, please correct your records and go to the Format > Number to pick one of the existing formats. Or proceed to Format > Number > More Formats > More date and time formats to create a custom one.
Once it's correct, you'll be able to calculate the average even with milliseconds in time units.
I'm trying to count total days between 2 dates, but my time is first and then the date, example: (column k) 15:45 03/23/2020 and (column m) 00:01 3/25/2020.
Is this even possible? Thanks!
Hello Sarah,
Google Sheets will recognize and calculate your values only if they correspond to one of the existing formats (Format > Number in the menu). In this particular case, you should switch date and time units places. Such cells will then be formatted as Date time, and you'll be able to subtract one from another. (Otherwise, they are merely text strings and you won't be able to do anything with them.)
EXAMPLE #3 does not work correctly. I cannot get the elapsed time by hours in a decimal format with that formula.
Hello Emily,
Please specify your start and end time and check how these cells (including the one for the result) are formatted.
Also, please provide the exact formula you're using and whatever it returns to you.
I'll try my best to help.
Hi Natalia,
I have data with multiple times in a day formatted as start and stop times e.g.
1/1/2020 17:19 1/1/2020 19:22
1/1/2020 19:35 1/1/2020 20:25
1/1/2020 21:08 1/2/2020 1:21
1/2/2020 0:04 1/3/2020 17:30
1/2/2020 9:05 1/3/2020 6:30
I am trying to determine how much total time was used EXCLUDING overlaps. Can you suggest how this might be accomplished?
I figured this out by subtracting the previous end time from the next start time. Assuming the example I gave was 2 columns wide and 5 rows long this would be B1-A2.
Hi Tom,
My apologies I didn't manage to reply right away. Good to know you found an easy solution :)
=HOUR(SUM(E9-D9))+(MINUTE(SUM(E9-D9))/60)
Hi Kris,
This was the formula for Madden's case.
I'd advise you to omit the excess SUM parts. Also, if the start and end times are on different days (but the difference doesn't exceed 24 hours), you need to:
1) introduce the ABS function that turns the negatives into positives
2) subtract the time difference from 24 hours only when the difference is negative
Taking all these into account, here's the formula:
=IF((E9-D9)<0,24-HOUR(ABS(E9-D9))+(MINUTE(ABS(E9-D9))/60),HOUR(E9-D9)+(MINUTE(E9-D9)/60))
Hi natalia.
I tried your formula it worked but if the time starts for example at 5pm and ends in 1am it gets and error since it shows negative. What should be the right formula?
Hello,
I am using this template for a weekly timesheet: https://docs.google.com/spreadsheets/d/1qgfcWdcrxVSJu0HoyUZp7w4pGrtHoH7iu-nMe5Qb7To/edit#gid=874047713
I am trying to change the "Total Hours cells to show a decimal format rather an hour and minute format. I have tried adding the multiplication by 24 to the formula in the "Total Hours" cells and setting them to Automatic formatting, but rather than displaying as a decimal, they show an h:mm am/pm format. For example, when I applied it to a cell displaying a total of 1 and a 1/2 hours (1:30), rather than becoming what I am aiming for (1.5) it becomes (1:30 AM).
What is the best way to achieve what I am looking for?
Hello Brett,
You are right by adding the multiplication by 24 to the formula in "Total Hours". If Automatic format doesn't work for you, try Numbers format instead.
Natalia, this is a jewel of an article with enviable commentary and an exemplary response log. By any measure, your piece on the functions of time in Google Sheets sets a high bar for technical articles web wide. It is an archetype of technical journalism that readers are lucky to encounter in any subject. You deliver highly functional content in an effective, economical that demonstrates a profound understanding (whether intuitive or intentional) of your readers' needs and motives. Your deft phrases comprise sufficient flourish to rank in grammatical excellence while avoiding even an occasional minute of waste on superfluous verbiage. Indeed, the syntax and structure of this article create a perfect, invisible corpus which dances in perfect time with the rhythm of your message. This dance of information in symbols carries the reader immediately, effortlessly away into the topic. Attention rapt the reader isn't left to count the steps (moments or words) until the relief of termination but is instead lost in interest of the information until the steps exhaust, their number definite, exact and the article ends with no more than the perfectly shaped phrase nodding to decorum and literary.
Then to remain in touch with your work and readers for years after original publication, actively responding to questions with distilled expertise...incredible and inspiring.
Well done, well done. I haven't a question of formula or function to put to you yet but can certainly see a future that includes several germane inquiries. As I encounter those questions, I hope to indeed put them to you and appreciate the subsequent education I know you will provide.
Keep onward in this most favorable spirit. Thank you for your effort and contribution to a better world.
Ray,
Thank you so much for such wonderful feedback! We'll try and keep up the good work. :)
Seconded. This is the interactive internet at its best! You must make Tim Berners Lee proud!
Thank you.
Thank you so much, Andrew!
I'm thrilled you also find our work here extremely useful :)
Hello Natalia!
I am kind of going off a couple of these commments.
First, I am trying to formulate so I can calculate regular hours worked.
So when I have
Time In 8:00 AM
Lunch Out 1:00 PM
Lunch In 2:00 PM
Time Out 4:00 PM
I am getting the right calculation by using =HOUR(SUM(B14-B11))+(MINUTE(SUM(B14-B11))/60) because we get paid for an hour lunch, so Im not worried about the lunch in this case. However, if I take a half hour lunch, and still get paid for the full hour, how do I add a half hour to this time without having to do it manually
EX
Time In 8:00 AM
Lunch Out 1:00 PM
Lunch In 1:30 PM
Time Out 4:00 PM
This should calculate out to 8.5 hours.
Thank you so much!
Hello Carly,
Will this workaround help?
=24*(B14-B11)-24*(B13-B12)+24*(E2)
E2 contains 1:00:00 which stands for the paid lunch duration. The logic is: work_day minus time_spent_on_lunch plus paid_lunch_hour
=IF(B5=0,"",IF(B5>0,(B8-B5)+(B12-B9),"OUT")) This is the formula I am using to calculate my hours however the sum total shows up with am or pm how can I remove this please?
thank you,
IN WORK 9:00 AM 9:00 AM 9:00 AM
LUNCH IN 2:00 AM 2:00 PM
LUNCH OUT 2:30 PM 2:30 PM
OUT WORK 5:30 PM 5:30 PM 1:00 PM
TIME WORKED 8:00 PM 8:00 AM 4:00 AM 8:00 AM
Hello Rose,
select the cell with the total, go to Format > Number > More Formats > More date and time formats, and choose the format you'd like to see in the cell.
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.
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.
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 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.
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
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,
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))*70+SECOND(ABS(C1-B1))/60)*A1)
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)
Thanks Natalia Sharashova. This formula worked for me.
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/if-function-google-sheets/
Checkboxes represent TRUE/FALSE, so the following should do:
=IF(A2=TRUE,NOW(),"")
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.