# Calculating time in Google Sheets: subtract, sum and extract 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.

## 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:

= End time - Start time

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 > More Formats > 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) * 24

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.

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, 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):

=(End time - Start time) * 1440

• 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):

=(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 > More Formats > More date and time formats. 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 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

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)`

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)

`=A2+TIME(0,0,30)`

Or substitute 30 seconds:

`=A2-TIME(0,0,30)`

• To calculate over 60 seconds, use simple maths:

=Start time + (N seconds / 86400)

`=A2+(700/86400)`

Or substitute 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.

1. 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.

2. To extract time, place the following subtraction formula into C2:

`=A2-B2`

3. 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:

1. Whether there's a header row.
2. If you want to get the Date unit.
3. If you want to get the Time unit.
4. 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 30 other useful add-ons at hand. Install it from the Google Sheets store to test everything out.

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.

## You may also be interested in

### 227 comments to "Calculating time in Google Sheets: subtract, sum and extract date and time units"

1. RIKEN PATEL says:

i have to convert data in 3-3 hours conversion ex- if one message is popup on 12:00am then display 12:00am to 3:00am time slot, other message is came on 7:00pm then display 6:00pm to 9:00pm time slot

• Hello Riken,

2. Antoinette says:

Please could you help with the following? I am not sure what kind of formula should be used. I wanted to make use of a screenshot of an example of what I want, but it won't allow me to paste a screenshot.

I need to set up a program for a dancing competition. I have different sections in the dancing competition, with each section have different time limits per performer. So here is an example:

Section: Solo Start time: 9:00 AM

Competitor 1 (Maximum time: 3min:30sec) (Grace time: 2min)
Competitor 2 (Maximum time: 3min:30sec) (Grace time: 2min)
Competitor 3 (Maximum time: 3min:30sec) (Grace time: 2min)
Competitor 4 (Maximum time: 3min:30sec) (Grace time: 2min)
Competitor 5 (Maximum time: 3min:30sec) (Grace time: 2min)
(Grace time after each section: 2min)

End time: ?

Section: Improvisation Start time: SAME AS END TIME - ?

Competitor 1 (Maximum time: 2min:0sec) (Grace time: 1min)
Competitor 2 (Maximum time: 2min:0sec) (Grace time: 1min)
Competitor 3 (Maximum time: 2min:0sec) (Grace time: 1min)

So I want the condition to be, if it is a solo, then maximum time per competitor is 3min:30sec with 2min grace for each competitor, with 2min grace after section. Start time is 9AM, so what will the end time be after section has ended?

Hope the above example makes sense.

• Hello Antoinette,

Feel free to share an editable copy of your spreadsheet with us (support@apps4gs.com) with 2 sheets: (1) a copy of your source data (2) the result you expect to get. I kindly ask you to shorten the tables to 10-20 rows. I'll look into it.

Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.

• Antoinette says:

Thanks so much Natalia. I have shared an editable sheet. It is just an example, as I cannot send the original sheet, because in our country there are laws against sharing people's information (especially children). I hope it makes sense. I do know what I want is quite complicated.

I created a new sheet – Copy of the Result – and entered all formulas there. I use the IF function to return the times per competitors based on the section mentioned in A2. You can add more conditions for other sections with the help of nested IF or IFS to reuse the same formula for other sections as well.

The total duration is a simple sum of all those time units.

Hope this helps! Please let me know if this is what you need :)

• Antoinette says:

Thanks so much for your help.
I was trying to make it very complicated, and it actually is not.
This really helped a lot.

• You're most welcome, Antoinette!

Glad I could help :) Have a lovely day as well :)

3. Jordan says:

Hey Natalia, hope you are well,

Just making sure you got my message on (May 20, 2022 at 5:14 pm)

• Hi Jordan,

Sorry I couldn't reply earlier. I replied a few minutes back though, please see my comment below.

If you still need some help with your formulas, please consider checking out the blog post we wrote about the IF function. Or share your spreadsheet with us (support@apps4gs.com) with 2 sheets: (1) a copy of your source data (2) the result you expect to get. Please shorten the tables to 10-20 rows.

Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.

• Jordan says:

Hi Natalia,

I have invited you to my Google Sheets for you, i think it would be the best way to figure it out.

• Hi Jordan,

Thank you for sharing the file. Seeing the whole picture, we've come up with another solution. I entered the formulas on the Copy sheet, please take a look.

4. Deep says:

Hi Natalia,

I have been reading through your blog posts and they are really tremendous. Need your support on one of my on-going problems which I am trying to solve through formula in Google-Sheets :

1. end_date = Available = (Today())
2. Number of months = Available in decimal = (4.1) months
3. start_date = ? (how to compute it)

Your help in this would be much appreciated.

Best,
Deep

• Hi Deep,

I'm sorry, I'm afraid I don't fully understand your task. Is that number of months shows the duration from the start time?

5. Victor says:

if I want to use =TEXT(B2-A2,"h:mm") to subtract time but I want it to display the result as 3h 2m how do I format it to do display it as such?

• Victor says:

I ended up figuring it out after days of trying things it was super easy. This is for anyone interested.

Times:
D2 - 5:18:00 PM
E2 - 8:20:00 PM

=HOUR(E2-D2) & "h " & MINUTE(E2-D2) & "m"

Result - 3h 2m

• Hi Victor,

Sorry I couldn't reply to you earlier. Happy to see you've got it figured out :) Thanks for sharing your solution!

• Victor says:

I ran into an issue today where it won't subtract backwards times like pm to am. Such as

12:23 AM - 9:50 PM

It gives an error because it's a negative number and I still haven't found a workaround

• Hi Victor,

The result I'm getting with this set of times is 21h 27m.

For me to be able to assist you better, please share a small sample spreadsheet with us (support@apps4gs.com) with 2 sheets: (1) a copy of your source data with the formulas that don't work (2) the result you expect to get. I kindly ask you to shorten the tables to 10-20 rows.

Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.

• Victor says:

Yes but that result is backwards and not correct. 12:23 Am is right after midnight. If you subtract midnight to 9PM that is only 3 hours we are trying to subtract not add.

I'm building a sheet to track all of my forex trading. I want to enter the time I start a trade and the time I end a trade and it tell me how long that trade went for. So I can keep an idea on how long these trades are lasting. Yesterday I started a trade at 9:50 PM and it went until 12:23 AM which is right after midnight. So I need it to subtract the time.

The result I'm looking for is 2h 33m

• Thank you for the update, Victor.

If you enter time without a date, Google Sheets treats these times as from 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.

• Victor says:

Can you give me a sample sheet that I can take a look at. I'm currently using two cells to collect this data. One for just the date and one for just the time.

4 cells total. Date, Time Start, Time End, Time completed

• Hello Victor,

If you'd like to take a look at how the formats work, you will still need to go to the Format settings to see the difference. So you can just literally (1) enter 9:50 PM into one cell and both, time & date, e.g. 06/02/2022 9:00 PM, in another, (2) select both these cells, (3) and go to Format > Number a few times and try to apply different formats, including those with the date unit. You'll instantly see what I mean about the dates.

For me to be able to help you better, I kindly ask you to share your sample sheet with us: support@apps4gs.com. You don't need to provide your entire document. A sheet with a few rows showing your source data and a sheet with the result you expect to get is more than enough. Thank you.

6. Balajinaik says:

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,"")

7. Jordan says:

Hello Natalia,

I use google sheets to do wages and I need a bit of help to sort an issue.

Regular work hours are 9am to 6pm (important to remember) and I'm trying to subtract overtime hours or minutes that my employees work.

For example, Start time is (7am) and the finish time is (9pm), I'd then like it to separate in another cell the 2 overtime hours from the starting point (7am-9am) and the 3 overtime hours from the finish point (6pm-9pm)

Look forward to hearing back from you.

• Hello Jordan,

Assuming your start time is in A2 and end time is in B2, try these IF formulas in C2 and D2:

• for C2:
=IFS(OR(A2<TIME(9,0,0),A2>TIME(9,0,0)),TIME(9,0,0)-A2,A2=TIME(9,0,0),"")
• for D2:
=IFS(OR(B2>TIME(18,0,0),B2<TIME(18,0,0)),B2-TIME(18,0,0),B2=TIME(18,0,0),"")
• Jordan says:

Hey Natalia, thank you for getting back to me.

So far the C2 format is working and I'm assuming that you missed out a comma in this area [A2TIME] but I have replaced it with [A2,TIME] and it's working.

For the D2 format, I am getting an error message, see the error message below.
[Wrong number of arguments to IFS. Expected at least 2 arguments, but received 1 arguments.]

• Jordan says:

I have also just noticed, that when I add the start time 10am. In C2 I get [23:00:00]. Is there any way to make it 00:00:00 if the start time was anytime after 9am?

• At the moment, the formulas calculate the negative number if the start time is after 9am. But if you'd rather see 00:00:00, then you need to write out each condition and the required result in the formula:
=IFS(A2<TIME(9,0,0),TIME(9,0,0)-A2,A2=TIME(9,0,0),"",A2>TIME(9,0,0),TIME(0,0,0))

• Jordan says:

Thank you ever so much for the help you have provided so far.

This format:
=IFS(A2TIME(9,0,0),TIME(0,0,0))

What would the format be for a time before 6pm?

• You're most welcome, Jordan :)

As for time before 6pm – if you'd like to add a condition for when the start time is before 6, just add the same part you quoted to the formula but change 9 to 6. If you mean you'd like to add conditions for the end time as well, please specify what you'd like to return if it's before/after/equals 6.

• Jordan says:

Hiya Natalia,

I'm not sure what I even wrote in the last reply, I'm confused myself lol.

So at the moment with the new [FORMAT] you provided, whenever I type anything after 9am it says 00:00:00 which is correct and id now like another format to do the same for when I write a time that is before 18:00:00 it should say 00:00:00 and it would only count the time after 18:00:00.

[FORMAT] you provided earlier:
=IFS(A2TIME(9,0,0),TIME(0,0,0))

Another note, not a huge issue, so when using the [FORMAT] and typing a time for example 10:00:00, it says 00:00:00. Is it possible to just have it blank using the '' ''?

• Jordan says:

I can see the confusion now, I'm trying to type the full [FORMAT] in the reply but for some reason it changed and doesn't post the hole thing?

Hopefully this works, this is what I'm trying to type:
=𝐈𝐅𝐒(𝐀𝟐𝐓𝐈𝐌𝐄(𝟗,𝟎,𝟎),𝐓𝐈𝐌𝐄(𝟎,𝟎,𝟎))

• Jordan says:

Its done it again, let me try another font:
[=̶I̶F̶S̶(̶A̶2̶̶T̶I̶M̶E̶(̶9̶,̶0̶,̶0̶)̶,̶T̶I̶M̶E̶(̶0̶,̶0̶,̶0̶)̶)̶]

• Jordan says:

I am trying to type the format you sent me on (May 19, 2022 at 6:49 am) Spain time.

• Hi Jordan,

For when the time is before 18, the drill is the same: you need to put that as a pair of "condition, it result" in the formula. Just like I did with 9, but only with 18 this time:
=IFS(A2<TIME(18,0,0),TIME(0,0,0),...)

If you prefer empty cells rather than 00:00, yes, feel free to use "" instead.
=IFS(A2<TIME(18,0,0),TIME(0,0,0),...)

Btw, I still suggest you look through this blog post about the IF function :) You just may find the answers to your questions there much faster than I can reply :)

• Hi Jordan,

My apologies, the formulas appeared broken due to > and < symbols. I've edited the formulas, please take a look now.

8. Sharon Mor says:

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?

9. Vernon Muma says:

Hello - trying to get google sheets to auto calculate clock out time for me - IE i put in / out time in for monday- thursday, and only in time on friday. Id like google to do the math for the final clock out, except it giving me negative total hours and the rest of the numbers dont seem to jive....

• Hello Vernon,

Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.

• Vernon Muma says:

• Thank you for the example, Vernon.

Your formula doesn't work because you use 40 as an integer, not as a number of hours. I've entered the correct formula to F6:
=B6+(TIME(40,0,0)-SUM(D2,D3,D4,D5))

10. ASha says:

I have a number which is 16.80
so since there is no 0.80 in minutes, it has to be converted to 0.60+0.20 so time becomes 17.20
Want to represent the time as 17.20
How do I do this

• Hello Asha,

Try this formula:
=IF(INT(A1)=A1,A1,ABS(0.6-(A1-INT(A1)))+CEILING(A1))

11. Jason says:

I'm having an issue where the formula =(f2-e2,"h:mm") is returning times that are off by a minute. For example cell e2 is 01:18 and cell f2 is 03:38. The above formula completes as 02:19 when it should be 02:20. This is happening on a significant portion of data I am trying to create.

• Hello Jason,

You formula doesn't work on my side at all (formula parse error). However a simple =F2-E2 returns 02:20.
For me to be able to help you, please share an editable copy of your spreadsheet with the formula that returns 02:19 with me: support@apps4gs.com. I'll look into it.

=E10-D10+G10-F10+I10-H10+K10-J10+M10-L10+O10-N10+Q10-P10

I am trying to calculate wages and I need the spreadsheet to multiply hourly pay by hour worked for multiple employees. Specifically I need a specific cell to multiply and add 6 cells together across 3 rows and it is returning decimal numbers and that cell is affecting another cell i have that is supposed to divide that information with another cell and because i am getting decimal numbers it is throwing off the end result that i am looking for.

I'm sorry but your question is not clear.

For me to be able to help you, please share a small sample spreadsheet with me (support@apps4gs.com) with 2 sheets: (1) a copy of your source data (2) the result you expect to get (the result sheet is of great importance and often gives us a better understanding than any text description). I kindly ask you to shorten the tables to 10-20 rows.

Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.

Thank you.

13. Declan McCormick says:

Hi, I have a sheet to work out staff hours and when time worked is over 24 hours for example 33:20:00 the formula i use to covnert this into a decimal amount of hours does not work and give me the amount of hours worked over 24 hours. I am using this formula below:

=(HOUR(E59)+MINUTE(E59)/60+SECOND(E59)/3600). I have tried changing the data type of the cell to [hh]:mm:ss but this still doesnt work. Do you have any suggestions?

Thank you!

• Hi Declan,

33:20:00 already shows the duration of hours (33). You can either multiply this by 24 (see Example 3. Time difference in hours, minutes, and seconds; make sure the resulting cell is formatted as Number) or change the time format into elapsed hours only.

14. Natalia says:

Hi, thank you for this info.
Just FYI, I think there is a typo in the extract the date/time section. Formula 2, says A2=B2, and I to match the image, it should say A2-B2.

• Hi Natalia,

That is definitely a typo. I've just corrected it, thanks a lot! :)

15. Jay says:

Hello,

How would I check to see if a time is within a time range when the range goes past midnight? (i.e: The time to check is 03:00 and the time range is (21:00 - 5:00). Any help will be appreciated, thanks!

• Hello Jay,

Is your time range in one cell or each time unit is in its own cell?

• Jay says:

Hello Natalia,

The range is in one cell (i.e 5:00pm - 5:00am). I can parse out the separate times, but I can't get it to realize that a time (i.e 3:00am) is in between the range since the time value is lower than the beginning of the range.

If it makes it easier to have each time in it's own cell, I can create helper columns (split) to separate them.

• Hello Jay,

Assuming that 3:00am is in A2, 5:00pm – in B2, and 5:00am – in C2, the following formula will return TRUE if the time falls within the range, otherwise, you'll see FALSE:
=OR(AND(B2>A2,C2>A2),AND(B2>A2,C2>A2))

16. Leah B says:

Hi Natalia, I'm sorry if this has already been asked/answered. I wondered if there was a way to add 2 seconds to a time formatted in a 00:00:00 AM/PM format. Ex( start time is 10:05:03 AM, and I want the end time to be 10:05:05 AM) I am trying to get a formula to add markers for video/audio edits quickly. This has been rattling around in my head for a while now, and I can't seem to figure it out! I would be super grateful if you knew how to accomplish this!

17. mattkwi says:

Hello! I am having difficulty doing a duration countdown. I have cells with a date & time something is due (call it A1) and another cell with the NOW() function in it (call it B1) , those both work. When I do B1-A1 it returns a duration which counts down, however it's not the correct time. I read that google uses a different reference year and add a +1 at the end which worked for some, but as they get closer to less than 24 hours they then add 31 days on the front. For one that has 17 hours 33 mins remaining it states 31 days 17 hours 33 mins remaining.

Any ideas?
best,
mattkwi

• Hello mattkwi,

Can you please specify the exact date time from A1 and the result your B1-A1 returns so I could understand your case better?

18. Torbjørn Oliver Mathisen says:

Hello, I'm trying to fix so that i can have name down in A, Times in minutes, seconds, and milliseconds in B, and the difference between them in C

Example
A B C
Name Time Difference
Rodney 1m:25s:754ms 0.000
Peter 1m:26s:516ms ??????

19. Else-Marie says:

Is it possible in Google Sheets to subtract 2 durations that are not linked to an AM/PM time? I am a swimming coach and I am hoping to use Sheets to calculate the difference between two race times. For example, I have a swimmer who swims the 50 yard butterfly. Her current time is 1:05.39 (mm, ss , ms) and her goal time is 35.83 (mm, ss, ms). Is there a formula I can use for this? One of the challenges is that I am subtracting different values (minutes and seconds capping out at 60 and then converting, milliseconds going all the way up to 100 before converting)

Thanks for any feedback or guidance you can give!

• Hello Else-Marie,

For Google Sheets to understand the exact format of the entered values, try the following:

1. Enter your time records as a full duration, including zeros for hours: 00:01:05.39 and 00:00:35.83
2. Go to Format > Number > Custom number format, and apply the following format to such numbers: hh:mm:ss.000

Then try calculate these durations, it should work.

• Andrew says:

Hi, I am also a coach that uses race times. I am having a very hard time as well.

I have many athletes that have times in (mm, ss , ms) ONLY.

I tried to use the format of hh:mm:ss.000 however it calculates to a 24 hour model. I am not using any hours at all in my splits for racing.

I need something like this
Athelte A- 1:04.75
Goal time- 1:03.09
I need the differences so it would be -1.66 so that my athlete knows that they need to drop 1 seconds and 66 mil seconds.

• Hi Andrew,

If setting the custom number format doesn't work for you, try the custom date and time format instead with the following:
Elapsed minutes (01):Elapsed seconds (01).Miliseconds (01)

Make sure to apply the same format to the resulting cells.

20. Vytaute Antipovaite says:

Hi,

I think I read most of the comments and unfortunately I cannot find a solution to my problem.

I need to add 52 hours to a date (08/11/2021 12:02:36) my problem is that I want to exclude the weekends.

In this case A1 = start date and my formula is =A1+time(52,0,0), I tried using =WORKDAY.INTL but it doesn't work.

Any help would be great and I love the your work.

• Hi Vytaute,

Your formula to add 52 hours is incorrect. Here I mention the correct way (how to add more than 24 hours).

And here's a formula to add those 52 hours ignoring the weekends:
=IF(OR(WEEKDAY(A1+(52/24))=1,WEEKDAY(A1+(52/24))=7),IF(WEEKDAY(A1+(52/24))=1,A1+DAY(1)+(52/24),A1+DAY(2)+(52/24)),A1+(52/24))

It adds 52 hours first and checks if the resulting date is Saturday or Sunday. If so, it adds 2 or 1 day correspondingly.

21. BJ says:

Is it possible to calculate elapsed time in Sheets using a defined start date/time, and the CURRENT date/time as the end point? For example, if I specified a start date and time, is there a way for it to output the number of days, or preferably hours that have elapsed since then?

• Hi BJ,

You can use NOW() and TODAY() functions instead of the specified end date/time, but these functions are volatile meaning they update themselves each time you open the spreadsheet or make edits anywhere in the file. Hence, your result will constantly update based on today's date/time.

But you can also use these functions to get the required results first and then turn them into values to stop further recalculations.

22. Alex says:

I am banging my head about this. I have a daily datetime stamps of a typical day at work starting at "10/22/2021 08:30:00 A" and ending at "10/22/2021 04:15:00 P". How can I filter out employees based on certain times of the day? Say 7 out of 10 employees start at 10/22/2021 12:15:00 P and end at 10/22/2021 03:15:00 P. How can I filter out those employees?

Any help is greatly appreciated.

23. Liz says:

Hi! I've used the following formula to find the duration of daily writing sprints:

=IF(INT(E3-D3)>0, INT(E3-D3) & " days, ","") & IF(HOUR(E3-D3)>0, HOUR(E3-D3) & " hour(s), ","") & IF(MINUTE(E3-D3)>0, MINUTE(E3-D3) & " minutes ","") & IF(SECOND(E3-D3)>0, SECOND(E3-D3) & " seconds","")

Now I want the sum of the duration times to find the total for the week. How can I do this?

Once I do that, I'd like to sum across multiple pages/sheets within the document so I can find the total for the month. Is this possible?

• Hi Liz,

For me to be able to help you better, please share a small sample spreadsheet with us (support@apps4gs.com) with 2 sheets: (1) a copy of your source data (2) the result you expect to get. The result sheet is of great importance and often gives a better understanding than any text description. I kindly ask you to shorten the tables to 10-20 rows. I'll look into it.

Note. We keep that Google account for file sharing only, please do not email there. Once you share the file, just confirm by replying to this comment. I'll look into it.

• Liz says:

I have shared my spreadsheet example with you as requested.

• Hi Liz,

I'm sorry for the delay in my reply, I was on sick leave. Please let me know if you're still looking for a solution, I'll look into your file then.

24. Gh says:

You must get over 800 things

25. Federico Gnoli says:

Hi,
I've got a starting date and a number of shift, If i sum the starting date and the shift it will find a delviery date, but I would love to avoid Holidays and week ends.
Could you help me?

• Hi Federico,

You need to use the NETWORKDAYS function. You will find instructions and usage examples in this blog post.

26. Ryan says:

Hi, is there a way to make it so it can calculate the time between two time frames if they're in the same cell?

i.e

8:00-13:00

• Hi Ryan,

=RIGHT(A2, SEARCH("-", A2))-LEFT(A2, SEARCH("-", A2)-1)

Don't forget to apply the Duration format to the result.

27. Tom Patterson says:

hey, I was just wondering how to calculate elapsed time from velocity and distance in google sheets.

• Hello Tom,

Could you please provide a couple of examples of your source records and of the result you'd like to get?

28. AC says:

Hi!

This is article is pretty helpful. Do you have any idea of how to calculate time within/outside of business hours? For instance, if Column A's data is 1/1/2021 5:20AM and Column B's data is 1/6/2021 2:30PM, I'd like the total time elapsed minus the time we're unavailable (I can provide the business hours - but in this example let's say we're completely closed Sat/Sun and are open from 10am-5pm Mon-Friday)?

Any help would be greatly appreciated.

Thanks

• Hi AC,

Thanks for stopping by our blog! :)

Assuming your start time is in A2, end time – in B2, opening time – F2, closing time – G2, the following formula will do the trick:
=(NETWORKDAYS(A2,B2)-1)*(\$G\$2-\$F\$2)+MOD(B2,1)-MOD(A2,1)

If there are any special holidays you'd like to take into account, you can set them right in the NETWORKDAYS function. You'll find a tutorial about it in this blog post.

29. Cait Z says:

Hi! First I absolutely love this post and all of your kind responses. This has been so helpful to me.
Second, I am trying to work out a formula I am hoping you can help with. I want a formula to calculate the duration of time (in decimal form such as 1.25 for an hour and 15 minutes) if the time start/end is entered manually. I can do this perfectly using your above suggestions IF I ENTER THE TIME IN MILITARY TIME ONLY.

I want this to calculate the exact same way by being able to enter the time as you normally such as start at 11:00 and end at 2:00 without it resulting in a negative number. Is there any way to do this?

THANK YOU SO MUCH IN ADVANCE!

• Hi Cait,

I described how to calculate the time difference in decimals in the 3rd example here.

However, if you use timestamps like this: start at 11:00 and end at 02:00, your spreadsheet won't understand that they belong to different days. You need to add days as well (19/08/2021 02:00:00) and then format cells to display the timestamp only. Then the time difference will be calculated correctly (a positive number).

30. Rezu says:

Can I insert Live Countdown in Google Sheets?

• Hello Rezu,

Yes, but we don't have a tutorial on our website. If you Google a bit, you'll find plenty of instructions, even demo videos, along with templates to use.

31. Rezu says:

In my Spreadsheet, Whenever I input a data in a row Timestamp is auto added in this format 8/17/2021 20.05.31. I want that a countdown of 48 hours from the inputted Date and time. Can you help me? What would be the formula for subtracting 48 hour from the timestamp?

32. Niklas says:

Hello there!
I´m trying to figure out how to get a timespan from a list of dates/times but cant seem to figure it out.
For example:

10/06/2020 19:28:55
15/06/2020 02:39:43
15/06/2020 06:30:04
16/06/2020 23:50:49

I want to see the elapsed timeframe between these different times.

10/06/2020 19:28:55 - 4 days, 5 hours, 10 minutes, 52 seconds till below time
15/06/2020 02:39:43 - 0 days, 3 hours, 50 minutes, 21 seconds, etc....
15/06/2020 06:30:04 - etc.etc.

Anyone know if this is possible and how i can make this appliable to a long list of dates and times?

• Hello Niklas,

Try a formula like this:
=INT(J4-J3)&" days "&TEXT(J4-J3,"h"" hours ""m"" minutes ""s"" seconds""")

33. Pepeto says:

Good night!

My goal is to add up all the hours worked in a month in a single cell.

That is, if I work:
07/04: 4:30 hours
5/07: 9 hours
06/07: 15 hours
07/10: 8 hours
07/15: 3 hours
07/20: 16 hours
I would like to get a cell that sums everything (= 55:30 hours)

I have tried with SUM and changing the format to "hours elapsed: minutes elapsed" or "hours: minutes" but I get absurd results (for example 2:30 or 26, never 55 and a half hours)

Is there a simple way to solve this? The fewer cells you use, the better.

• Pepeto says:

Ok, I found the reason why SUMA was not working for me.

In the working range from 18h to 24h, the "24" interprets it as 00: 00h but NOT the 00: 00h at the end of the day and yes as 00: 00h at the beginning of the day (that is, -18 hours instead of +6 hours)

I managed to change that and it already works fine for me.

• Narendra says:

hello,

how you solved it ?
what is formula of this problem ?

34. Alexa says:

Hi!
I am trying to use a formula to determine hours worked in decimal form when we have shifts that often go past 12am. I need a way to do this where the outcome is not negative and can be all done in one formula, is that possible?

For example:
I have tried using both:
=TEXT(B2-A2, "h.m")-0.3 to try and get hours worked minus break time but this will not convert to decimal
= ((B2-A2)*24)-0.5 but this one causes negative numbers for any shifts that go past 12am

• Hi Alexa,

You can turn negative numbers into positive by embedding your formula into the ABS function.

If this still doesn't solve your task, please consider sharing a short sample table with us – support@apps4gs.com. I'll look into it.
Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.

35. Zalmen says:

hi can anyone help
How To Convert Time To Decimal Over 24 Hours In Google sheet?
=HOUR(A1)+MINUTE(A1)/60

• Hi Zalmen,

I mention decimals in the following section: "Example 3. Time difference in hours, minutes, and seconds.". It's in the first part of the blog post, please take a look.

Also, here are some comments where I suggested formulas: Also, here are some comments where I suggested formulas: SUM and convert to decimal, format values correctly to see decimals.

If you're still unable to get the result, please specify (1) the exact time value you're trying to convert, (2) the formula you're using (3) and the result you're getting. I'll do my best to help.

36. Kim says:

Hello,
I am trying to create a google chart that automatically populates the 24 hour mark from the date and time I input in column A.

Example:
A
6/17/2021 8:09 pm

B- will automatically populate the date and time 24 hours later
6/19/2021 8:09 pm

• Hello Kim,

Just make sure the Data range that is used by the chart includes all cells (even empty ones) that will be filled in future. For example, B2:B100. Once they are filled, they will be shown in the chart as well.

If that's not really the problem, consider sharing an editable copy of your spreadsheet with us (support@apps4gs.com).
Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.

I'll look into it and try my best to help.

37. Mike says:

if I want to multiply time?

For example I have a base time of 0:08 minutes and I want to multiply it by for eaxmple "10", in which the result would be 1:20 hours?

I've tried =0:08*10 but it gives me #ERROR!

What should I do?

• Hello Mike,

You're getting the error because when you enter =0:08*10 that 0:08 is treated as text. This happens because Google Sheets doesn't see date and time the same way you see them. Please read about it here.

For the calculation to work correctly, I'd advise you to enter 0:08 in a separate cell. This will let Google Sheets recognize the record (you'll see that the format in a cell will change to 00:08 while the formula bar will show 00:08:00). Then reference this cell in a formula from another cell: =A2*10
Or use a formula like this: =TIME(0,8,0)*10

38. Tari says:

Hi Natalia,

First of all, thank you for the amazing article and responses in the comments!
I'm evaluating the time it takes to do certain tasks, and I was able to build a table with the correct duration values. This table includes values over 24 hours of duration, for example, "25:04:00".

In the table, everything works great, the conditional formatting for values over 24 hours works, and everything is perfect. However, I created a chart from this table, and the chart doesn't work as well.

It's a combo chart with bars representing the duration over time, and lines to represent the objectives and targets. Taking the example above, instead of 25 hours, the bar represents only 1 hour. It resets at 24 hours.

Even worse, another chart that is a copy of the first but feeds on different values, will reset values at 12 hours.

I can't seem to find any setting to control this. Can you help me?

• Hi Tari,

For me to be able to help you with the chart, please share an editable copy of your spreadsheet with your table and the chart with us (support@apps4gs.com). If you have confidential information there, you can replace it with some irrelevant data, just keep the formats and durations intact.

Note. We keep that Google account for file sharing only, please do not email there. Once you share the file, just confirm by replying to this comment. I'll look into it.

39. Susan says:

I'm trying to tally up 2 different columns of times and determine the difference between them. Both sets of columns have an end time, subtracted by the start time to determine duration. I have the result of the formula formatted as "[h]:mm." Most of the time, the value is positive, more time was spent rather than what was estimated. However, there are some times when the actual hours worked were less than what was expected. I'm trying to keep a running tally of hours 'owed' as opposed to extra.

I am working on an old computer with an old version of Excel. It doesn't have as many of the formatting possibilities as you have listed in your examples above. The formula works when it returns a positive time duration, but the negative time is expressed as "####" because it says that there is no 'negative' time? Is there a workaround for this? Do I need to convert the time value to "real" numbers, keep a running sum, then put them back into time format for a total number of hours minutes? Could I incorporate an if/then statement?

I'm not as familiar with Goggle Sheets. I am going to be sharing this data with someone via Sheets and if there is a better/easier way to use it directly I can just avoid Excel altogether.

• Hello Susan,

The instructions in this blog post are for Google Sheets. For Excel, you will find them in this article. There Svetlana mentioned how to make Excel display negative time. Hope this helps!

But if you're okay working with Google Sheets, there's only one version of Sheets and it shows negative time by default. Feel free to calculate everything in Google Sheets and let me know if it doesn't work as you expect there.

40. Jesse says:

Hello, I am trying to add up all the time in one column and have it represented as dd:hh:mm but I just can't seem to do it. Im wondering if you could help. I will give an example directly from the sheet. Also, they are all represented as hh:mm. So c1, 0:41 c2, 0:53 c3, 2:22 c4, 3:28 c5, 9:17 c6, 6:49 c7, 1:56 c8, 3:25 c9, 2:43 c10, 6:28. Like asked how would I add the whole column with said outcome? (So if I update it time will change) Also, could these be averaged but with an outcome of hh:mm so I could find average hours per day? (Each c1, c2 ect is a day). Thank you so much this would be very appreciated.

• Hello Jesse,

I answered the same question just a few days ago, please have a look :) The same formula will work for you. If you have your SUM in C11 (showing 38:02 as a result), reference C11 in the formula from that comment of mine, and you'll get 1 day 14 hours 2 minutes.

As for the average, it is usually calculated from a few values. I'm not sure how you want it to apply to each cell separately. Perhaps, you mean to round the values to full hours?

• Jesse says:

OH MY GOODNESS. Thank you this is awesome. ;)

• Jesse says:

Oh for the average just have it as it is hh:mm. I assume you naturally find the average then convert it with the formula you showed me. Hmmmm

• Jesse says:

Wait nevermind I figured out how to do the average.

• It's my pleasure, Jesse :) Good to know you found the answer to your second question as well!

41. Moss says:

Sorry if this is a silly question. I want my time differences displayed as a decimal of an hour (1.5 hours instead of 1:30:00) How can I do this? Thanks!

42. John Peters says:

Hi Natalia,

Firstly, thank you for the work you have done here, it has helped me a lot. However, I am stuck at the final hurdle.

I am calculating the total duration spent across a month and need to display the result in dd:hh:mm. I have the hh:mm for the month summed, so it's just this final part of the puzzle that has me stuck. Currently, I have a calculation that sums to 29:09 (so 29 hours and 9 minutes) which is correct and when I try to format that into dd:hh:mm it returns 31:05:09, however, it should be 01:05:09 (1 day, 5 hours and 9 minutes).

Hope you can help.

Thanks
John

• Hi John,

=INT(A2)&":"&IF(Hour(A2)<10,"0"&Hour(A2),Hour(A2))&":"&IF(Minute(A2)<10,"0"&Minute(A2),MINUTE(A2))

• John Peters says:

That's great! Thank you!

43. Mark says:

In regards to google sheets and changing the format. I imported a spreadsheet which has call log times for my employees. I am trying to standardize and find out the daily time they spend on the phones helping patients so I can track who is pulling the heaviest call volume. My report that exports from my phone company only puts the format as 10mins or 56 secs, is there a way or formula to convert that into the standard 00:00:00 timing format so that I can add up their total time? I am not seeing a way to do this and it is looking like this may be a manual process. Any help would be greatly appreciated.

• Hello Mark,

You can check if your cells contain 'mins' or 'secs' and extract the numbers already converted to minutes or seconds accordingly. Here's a formula:
=IF(ISNUMBER(SEARCH("mins",A2)),TIME(0,LEFT(A2,SEARCH("mins",A2)-1),0),IF(ISNUMBER(SEARCH("secs",A2)),TIME(0,0,LEFT(A2,SEARCH("secs",A2)-1)),""))

Paste this formula where you want to see the result, change A2 to a cell you're checking, and make sure to change the number format of the resulting cells to Duration.

Hy! I need to help to solve problem. When employee atrandece time is greater than office time than cell is colour red . How can it possible plz

45. Maria says:

I may be missing something, but I cannot find the solution to how to avoid a negative calculation of time when they're on two different days. I'm trying to create a very simple time sheet, but if someone has a start time of 11pm and end time of 4am this shows a total of -19 hours. How do we fix this within the formula? Thanks.

• Hello Maria,

Please look at Example 1 in this part of the blog post. There's a similar case in row 3 and the subtraction works. Also, make sure you format your result cell correctly.

If you still can't get the required result, please tell me what you use as the start and end time exactly and specify your formula.

46. manuel says:

Thank you very much for the guide!
But I still experience an error that is testing my patient very hard...
Basically when i put 13:00 in a cell (after applying the hh:mm format) google sheet doesn't recognize that as a number or time and it says that it is text...
And no formulas works...
Only way that i found is to imput the time using . instead of : but it's a bad workaround

Anyone got this problem too?
Thanks!

• Hi Manuel,

The date and time format and separators usually depend on your spreadsheet locale, time zone, and display language. I described it in this blog post, please have a look.

47. kantharaju says:

hello All, I have tried with formula, But giving the result as below, Please suggest what's wrong.
Eg : 05/06/2020 18:22:51 - 04/06/2020 16:42:24 = 721.6741667
Please suggest how to solve this
Thanks
Kantharaju

• Hello Kantharaju,

Try selecting your resulting cell and changing its format to Duration.

48. Mauro says:

I've two cells formatted as Date Time and i cannot do any calculation..
why? :(

• Hello Mauro,

For me to be able to help you, please specify the formula that doesn't work and describe what lies in the ranges your formula refers to.

49. Izhar says:

Hi,
This is a great website to learn excel from. I have a question, please reply me.
I have two columns, in one of the column there is duration and in the other one I want to add only the hours and not the minutes and seconds. For example: 1:45:30, 2:26:47 and 0:35:17 in column A, should appear as 1:00:00, 2:00:00 and 0:00:00 respectively in column B.

Is there any way

• Hi Izhar,

Here's a formula for you to try if you work in Google Sheets:
=A2-MINUTE(A2)/1440-SECOND(A2)/86400

The basics of these are described in this paragraph above.

For formulas with time in Excel please visit this blog post: calculate time in Excel

50. David Ha says:

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.

51. Sarah says:

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.)

52. Emily says:

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.

53. Tom says:

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?

• Tom says:

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 :)

54. Kris says:

=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))

55. Kris says:

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?

56. Brett Peart says:

Hello,

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.

57. Ray says:

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. :)

• Andrew Downs says:

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 :)

58. Carly says:

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

59. rose says:

=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.

60. Thaylin says:

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?

• Thaylin says:

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.

• Thaylin says:

Thank You for the help.

61. Jeff says:

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.

62. Michael Pyke says:

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.

63. Rachel says:

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.

64. Ivan says:

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.

• 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.

65. Donna says:

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!

Thank you very much for the clear description of your task. :)

=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'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?

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)

• Justin says:

Thanks Natalia Sharashova. This formula worked for me.

68. Fred Cane says:

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

69. Michael Baun says:

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))

• Michael Baun says:

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.

70. Katie says:

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.

71. Mac says:

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:).

72. Mac says:

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.

73. k says:

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.

• K says:

Thanks.

74. Kurt says:

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.

75. Lauren says:

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(...),...)

76. Peter Bain-Hogg says:

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,

=HOUR(A1)*3600+MINUTE(A1)*60+SECOND(A1)

• bryan says:

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.

77. Katrina says:

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.

78. cedrik lapointe says:

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:

Open | 9:00 AM
Close | 6:00 PM

Name | Start time | Closing time
Name1 | 2019-06-10 10:00:00 | 2019-06-22 15:00:00

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)

79. Patty says:

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"))

• Lint says:

=ArrayFormula(IFERROR(if(Len(C2:C),(int(K2:K-C2:C)&"d "&text(K2:K-C2:C-int(K2:K-C2:C),"HH:MM:SS")),)))

• Hello Lint,

I'm sorry but we need to see your data to be able to help. Please consider sharing an editable copy of your spreadsheet along with the formula with us (support@apps4gs.com). If you have confidential information there, you can replace it with some irrelevant data, just keep the format.

Note. We keep that Google account for file sharing only, please do not email there. Once you share the file, just confirm by replying here.

We'll look into it and try to help.

In the meantime, you can use our Multiple VLOOKUP Matches add-on for Google Sheets to pull the necessary data without the weekends first and then process it the way you need.

80. Robert Simmons says:

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.

81. Desire says:

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!

82. Lauren says:

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?

83. Ilham says:

Halima’s slept at 2200h if she woke up at 0600h for how long had she slept.

84. Peter says:

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.

85. Mike Wurlitzer says:

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!

• Honey LaBronx says:

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

86. Dave Bacon says:

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!

• Doug says:

Dave:
Have you tried to use this in the Custom section of the Format Cells? mm:ss.000

87. Ola says:

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

88. G says:

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?

• Doug says:

G:
How is the cell duration h formatted?
The issue may be one of formatting the cell the way you want it to display.

• Zehra says:

Solved the same problem by changing the number format in the sum cell to [h]:mm:ss instead of h:mm:ss

• Zehra says:

Actually the above was in onedrive excel, not google sheets

• Travis says:

I also need to fix this. Trying to convert 2 week pay period that is reported in hours 72:30:00 into a decimal of 72.5 hours.

• Hello Travis,

You need to multiply a cell with that duration by 24 and format the resulting cell as a number.

I mention decimals in the following section: "Example 3. Time difference in hours, minutes, and seconds.". It's in the first part of the blog post, please take a look.

89. Rachael says:

Thank you! This just saved me so much time!

• Alfonso says: