Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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

 Date        | Start       | End         | Minutes
 ------------+-------------+-------------+-------------------------
 1/11/2012   | 11:39       | 12:41       | ?!
 ------------+-------------+-------------+-------------------------
 1/11/2012   | 21:40       | 23:01       | ?!
 ------------+-------------+-------------+-------------------------     
             |             |             | 

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

share|improve this question
    
Hi Stefano, perhaps you can take some time and evaluate the answers given. –  Jacob Jan Tuinstra Aug 2 '13 at 18:58
1  
@jacob As far as I can work out, my answer still works. I'll try to clarify and improve it a bit. –  Stefano Palazzo Aug 5 '13 at 6:49

4 Answers 4

up vote 13 down vote accepted

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
share|improve this answer
    
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

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.

share|improve this answer

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

share|improve this answer

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

HTH

share|improve this answer
    
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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