I want to sum the begin and end with the QUERY function, to be able to subtract them, see table:

day       |   begin   |    end     |
monday    |  9:00:00  |  13:00:00  |
tuesday   |  9:00:00  |  14:00:00  |
wednesday |  9:00:00  |  12:00:00  |

I was only able to perform a summation, by setting the format of the time notation to NUMBER.

How can I sum a time notation (duration) in the new Google Spreadsheet with the QUERY function?


Unfortunately, to the best of my knowledge, there is no straightforward way of calculating time differences inside a Sheets QUERY clause. The best that I can think of is to convert the time to a serial value (using the hour and minute scalar functions), perform the difference calculation, and format the resulting column as time:

=QUERY(A:C,"select A, hour(C)/24+minute(C)/1440-hour(B)/24-minute(B)/1440 label hour(C)/24+minute(C)/1440-hour(B)/24-minute(B)/1440 'Difference'",1)

| improve this answer | |

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.