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?
Google has added a new number format called Duration. Set your start and end fields to 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.
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.
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.
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
If you add the following formula in D2, then the minutes are calculated automatically:
=ARRAYFORMULA(IF(ISBLANK(B2:B)=FALSE,((C2:C-B2:B)*24*60),""))
The difference between the times, as per decimal format, is expressed in days. Therefore multiplying times 24 time 60 will yield minutes
There is one prerequisite: column D needs to be formated as 'normal'.
See example file I created: Delta Time
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.
function toEpoch (data) { return new Date(data) / 1000; }
– user1278519
Apr 23 '17 at 18:06
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:
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.
Use the formula =abs(end time - start time). This gives you an absolute value, so there won't be negative time values.
If you want your time delta to be measured in days, use
=DAYS(end_date, start_date)
So much simpler: look at this 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. http://www.excelforum.com/excel-general/471757-calculating-time-difference-over-midnight.html
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.
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.
I use this formula :
=HOUR(C2-B2)*60+MINUTE(C2-B2)
then
Format -> Number -> More Formats -> Custom number format -> #,##0
=(C2-B1)*1440 then set the format to "Plain Text".
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.
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;
