103

I've got a Google Spreadsheet that looks roughly like this:

 Date        | Start time  | End time    | Minutes
 ------------+-------------+-------------+-----------
 1/11/2012   | 11:39       | 12:41       | ?!
 ------------+-------------+-------------+-----------
             |             |             |

Right now if I fill in the number of minutes between the two times of day by hand. Is there a straight-forward way to calculate a time delta and have the spreadsheet do it for me?

Improve this question
0

14 Answers 14

Active Oldest Votes
97

Google has added a new number format called Duration. Start by formatting the start and end fields to Format -> Number -> DateTime or Format -> Number -> Time and your calculation field to Format -> Number -> Duration

Once you have done that you can subtract the fields to get the difference as noted by Stefano Palazzo in his answer.

Improve this answer
4
  • 2
    Tiny addition, Start and End fields can also be DateTime(Format > Number > DateTime), which also works.
    – Beytan Kurt
    Jun 14 '20 at 16:29
  • @BeytanKurt Isn't that what I have there?
    – Jacob Hulse
    Oct 12 '20 at 19:18
  • It seems answer has been edited to include what I said @jacob
    – Beytan Kurt
    Oct 13 '20 at 14:54
  • @BeytanKurt Yep it was, by me, after you pointed that out! lol
    – Jacob Hulse
    Oct 13 '20 at 22:09
64

Yes, If your time fields are properly formatted (click Format → Number → Time) you can just add and subtract times:

=C2-B2

or

21:58:00 - 20:44:00 = 1:14:00

This will give you the time delta as HH:MM:SS. And if you want to calculate the number of minutes, you can use the Hour(), Minute() and Second() functions on that field:

=(Hour(D2) * 60) + Minute(D2) + (Second(D2) / 60)

Of course, if there are leap-seconds, time zone changes, or if an event takes longer than 24 hours, you will still have to adjust the results manually.

A warning

If one event stretches past midnight, say from 23:50 to 00:10, this will show up as a negative time!

In oder to have these events handled 'correctly', you can either put "24:10" or split the event into two.

A Better way

Even though it's a bit harder to input data, the most reliable way to do this is to mark the beinning and end field as "Date Time" and the delta field as "Hours", which will look like this:

Beginning          End                  Delta
8/1/2013 0:00:00   8/2/2013 12:30:00    36:30:00
Improve this answer
4
  • This doesn't appear to work anymore. I think you need to choose Format > Number > Hours (vs. time). I get a very odd decimal number when I try to subtract simple hourly difference.
    – Elijah Lynn
    Jul 5 '13 at 17:27
  • @ElijahLynn this odd number are the number of days. Multiplying times 24 and 60 will give you the number of minutes,
    – Jacob Jan Tuinstra
    Jul 21 '13 at 12:36
  • 1
    If someone wants to get date and time in one cell, he can merge just by addition: A1 + B1.
    – Denis Kutlubaev
    Aug 3 '16 at 9:02
  • 'A warning' is important. I have not seen that anywhere else to be declared but was looking for it. For me it is not a warning but an essential part of handling differences between two times. It is just natural to not have its order declared in the first place.
    – Dirk Schumacher
    Mar 28 '20 at 8:46
9

If you add the following formula in D2, then the minutes are calculated automatically:

Formula

=ARRAYFORMULA(IF(ISBLANK(B2:B)=FALSE,((C2:C-B2:B)*24*60),""))

Explained

The difference between the times, as per decimal format, is expressed in days. Therefore multiplying times 24 time 60 will yield minutes

Remark

There is one prerequisite: column D needs to be formated as 'normal'.

Example

See example file I created: Delta Time

Improve this answer
9

I've done a lot of experimentation. This is the easiest way to calculate a time delta in Google Spreadsheets. Format the cell containing the formula like this:

Formula cell formatting

Format > Number > More Formats > More date and time formats, delete "second" and :. Then, format the End time and Start time cells like this: h:mm am/pm.

Input time cell formatting

Use the formula =abs(end time - start time). This gives you an absolute value, so there won't be negative time values.

Improve this answer
9

So much simpler: look at this answer from Excel Forum:

B2: 23:00
C2: 1:37
D2: =C2-B2+(B2>C2)

Why it works, time is a fraction of a day, the comparison B2>C2 returns True (1) or False (0), if true 1 day (24 hours) is added.

Improve this answer
1
  • I was about to add this answer but noticed that it's been added already. Good job. This is definitely the simplest way to solve the problem presented by Stefano. It's also important to point out that the D2 cell must be formatted as [m]: it's necessary to select the D2 cell, then click on Format > Number > More Formats > Custom number format, then add [m] and click Apply.
    – Yuri Sucupira
    Dec 27 '19 at 4:44
7

For a more robust solution, we uses a custom Function.

1. Adding the custom function

Using the Script Editor (follow instruction in https://developers.google.com/apps-script/execution_custom_functions) - writes:

function toEpoch (indate) {
  return indate.getTime();
}

2. Add formular

Then in the cell, writes:

=(toEpoch(C2)-toEpoch(B2)) / 60*1000

Which convert the difference in Epoch milliseconds into minutes.

Improve this answer
1
  • 1
    ... I did this, to make sure to convert it and convert it to seconds already (from milliseconds): function toEpoch (data) { return new Date(data) / 1000; }
    – user1278519
    Apr 23 '17 at 18:06
5

Disclaimer: I am posting this answer that doesn't exactly answer the asker's question because this is the answer I needed when this question was the first result on Google, and I want to help the next person who has the same question I did.

This may not be the prettiest formula, but the result is the prettiest I could manage. Here's the Google Sheets formula that I used to articulate the difference between today and something that happened a while ago, assuming the F2 cell contains a date:

=IF(DATEDIF(F2,TODAY(),"Y")>0,DATEDIF(F2,TODAY(),"Y")&" year"&IF(DATEDIF(F2,TODAY(),"Y")=1,", ","s, ")&DATEDIF(F2,TODAY(),"YM")&" month"&IF(DATEDIF(F2,TODAY(),"YM")=1,", ","s, "),IF(DATEDIF(F2,TODAY(),"YM")>0,DATEDIF(F2,TODAY(),"YM")&" month"&IF(DATEDIF(F2,TODAY(),"YM")=1,", ","s, "),""))&DATEDIF(F2,TODAY(),"MD")&" day"&IF(DATEDIF(F2,TODAY(),"MD")=1,"","s")

Assuming today is 2020-03-09, here are some example results of this formula:

column F   | column G
--------------------------------------
2020-02-14 | 24 days
2019-10-08 | 5 months, 1 day
2019-06-14 | 8 months, 24 days
2019-04-05 | 11 months, 4 days
2019-03-09 | 1 year, 0 months, 0 days
2019-02-01 | 1 year, 1 month, 8 days
2018-12-07 | 1 year, 3 months, 2 days
2018-03-04 | 2 years, 0 months, 5 days
2018-01-09 | 2 years, 2 months, 0 days

I ensured that:

  • Pluralization is handled correctly on all units
  • The highest-magnitude time units are hidden if all 0
  • Lower-magnitude time units are shown even if 0 if higher-magnitude time units are above 0

You can add &" ago" to the end of the formula if you want it to say " ago" at the end.

Improve this answer
4

If you want your time delta to be measured in days, use

=DAYS(end_date, start_date)
Improve this answer
2
  • That's true. However, I came here trying to find the difference in days (and came back later to give an answer when I found it elsewhere), which leads me to the assumption that my answer could benefit others in my same situation. Since it's such a related topic, I didn't think it was worth opening a new question, especially since the title doesn't specify the unit of measure. But that was just my reasoning. What do you think?
    – e18r
    Oct 7 '17 at 20:37
  • @pnuts If you find an answer that is useful but slightly out of the scope of the question, you can also just edit the question (in this case to say minutes or hours or days or whatever). I'm am just one guy, the question has 100,000 views.
    – Stefano Palazzo
    Oct 9 '17 at 15:07
4

You may try TIMEVALUE() as well.

In the above case, the solution would be:

(TIMEVALUE(End Time) - TIMEVALUE(Start Time))*24*60 will give you the time difference in MINUTES.

Improve this answer
1
  • Welcome Sujeeth! Good answer, I added some formatting to make it easier to read.
    – Vidar S. Ramdal
    Oct 16 '18 at 13:18
3

In Google Sheets, I used the formula as below

=Round((hour(A2-A1)*60 + minute(A2-A1))/60,2)

to give me the difference in decimal hours.

Improve this answer
0
0

I use this formula :

=HOUR(C2-B2)*60+MINUTE(C2-B2)

then

Format -> Number -> More Formats -> Custom number format -> #,##0
Improve this answer
-1

=(C2-B1)*1440 then set the format to "Plain Text".

Improve this answer
0
-1

For my personal application:

StartTime (columnA)| EndTime(Column B)| Date (Column C)

This worked for me:
=if(B4-A4<0,(B4+C4+1)-(A4+C4),B4-A4); Format cell as Format\Time\Duration.

For true condition: formula inspired by Stefano Palazzo's comments.

Improve this answer
1
  • p.s. date is associated with StartTime even if StopTime rolled over to the next day, which is why I added +1 to the date for EndTime. If your EndTime rolls over by more than a day, +1 obviously won't work anymore.
    – Bob K
    Jan 10 '17 at 4:41
-2

I agree that the time delta is expressed in days so to get back to say minutes you should multiply the difference by 1440, which is 24 x 60.

I had this problem today and thought I would mention the other simple method of getting the current time in a Google Docs spreadsheet is to enter CONTROL: in the cell. You can use this for both the Start and End Times cells.

If you need to enter the current date in a cell, then this is achieved by CONTROL;

Improve this answer
1
  • 1
    Thanks for sharing, but it isn't an answer to the question. Read more tour that in our help center. Welcome to Web Applications !!
    – Jacob Jan Tuinstra
    Nov 6 '13 at 6:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.