Select the difference between two times in QUERY

Question

I have a sheet with three columns: Name (A), startTime (B), endTime (C), and have been trying to run a Google Sheets QUERY that will show the duration (endTime - startTime).

The problem appears to be how the QUERY is recognizing the startTime and endTime values.

I've formatted the cells to look like: 10:00AM, 4:30PM. When I click on the original sheet on the same value they appear as: 10:00:00 AM, 4:30:00 PM.

When I click on the value after performing the query:

=QUERY(Sheet1!A:C, "select A, B, C")

it looks like: 1/1/1900 10:00:00, 1/1/1900 16:30:00.

I have tried:

=QUERY(Sheet1!A:C, "select A, C-B")

Error:Can't perform function difference on values that are not numbers

=QUERY(Sheet1!A:C, "select A, dateDiff(C,B)")

Error:Can't perform function 'dateDiff' on values that are not a Date or DateTime values

Can you share an example sheet ? That'll help solving this issue. — JPV, Apr 05 '15 at 20:42

score 5 · Answer 1 · edited Jun 07 '17 at 16:18

5

I was able to get the duration with this Query:

=QUERY(
    QUERY(
        QUERY(
            Sheet1!A:I
            "select A, hour(B)+minute(B)/60, hour(C)+minute(C)/60"),
        "select Col!, Col3-Col2"),
    "select Col1, sum(Col2) group by Col1")

I really hope there is another way. :(

edited Jun 07 '17 at 16:18

Michael

8,362
6
61
88

answered Apr 11 '15 at 06:26

Nulpoints

81
1
5

score -2 · Answer 2 · answered Apr 05 '15 at 13:48

-2

Your use of datedif is incorrect, This calculates the difference in two dates, not times.

=datedif(start date, end date, unit)

this is how it should be used, eg. =datedif(A2, A3, "D") will compare a2 and a3 and give the difference in DAYS.

If, for your needs, you need the difference in the dates by DAYS and TIME. Then you will need datedif and something else to look at the time element.

I would suggest having a look at TIMEVALUE function. To get the difference between two times, you can try something like

=timevalue(a2)-timevalue(a3)

This will deduct one time from another giving you the difference.

Hope this helps get you started

answered Apr 05 '15 at 13:48

Munkey

958
11
28

3

Thank you for answering. I may not have been clear in my question. I'm at doing this in a query. Google's Query doesn't appear to have timevalue() as a function. And dateDiff() in Query only takes two parameters and does accept dateTime as a variable. However it looks like the problem with that is that it truncates the time anyway, so I can't use dateDiff. – Nulpoints Apr 05 '15 at 17:21
Sorry, I had hit send too quickly. The first time :-) So no, it still doesn't solve my problem :( – Nulpoints Apr 05 '15 at 17:24

Select the difference between two times in QUERY

2 Answers2