Need to find the duration using query in google sheet

Question

Have rfid attendance data in google sheet where it has in and out time.Need to have how many hours an employee had worked for a given day. Have used query statement and was able to pull out min and max for a given day.Struck at need to find the duration HH:MM worked

https://docs.google.com/spreadsheets/d/1AbcTQ8CdmPO7KjtMFPWc1QK7rVnf1q0K51U_WAMKlqw/edit?usp=sharing

Attached the google sheet do have a look at the query code in G1 need to find the duration in K column

QUERY(A:D,"Select A,B, Max(C), Min (C),Max(C)- Min (C) Group by A,B Label A 'Key',B 'Date',Max(C) 'Maximum', Min (C) 'Minimum', Max(C)- Min (C) `Duration`",0)

so how to find difference between hours in query statement of googlesheet

Diego Diego · Accepted Answer · 2019-11-11T09:57:53

In your query formula, Sheets provides an error message that seems to be about the labels, so delete the label clause

Label A 'Key',B 'Date',Max(C) 'Maximum', Min (C) 'Minimum', Max(C)- Min (C) `Duration`

Now, after deleting the label clause, Sheets provides another error, stating

Can't perform the function difference on values that are not numbers.

So the problem is that you can't subtract Min(C) from Max(C) because the value in C is a time, not a number.

I don't think there's a great way to fix this within your QUERY() formula, but we can fix the data in the spreadsheet so that it works. In Column E, use =VALUE() to convert the times from Column C into numbers. For example, E2 will be =VALUE(C2).

Now we just need to use those new values in your query (instead of Max(C)-Min(C), use Max(E)-Min(E)) and include the format clause.

=QUERY(A:E,"Select A,B, Max(C), Min(C), Max(E)-Min(E) Group by A,B Label A 'Key',B 'Date',Max(C) 'Maximum', Min(C) 'Minimum', Max(E)-Min(E) 'Duration' Format Max(E)-Min(E) 'HH:mm'",0)

Need to find the duration using query in google sheet

1 Answers