CODEWITHSUNDEEP
google-sheets
Tim
Tim

Reputation: 11

Query based on datetime interval

I would like to find out which driver is available during a certain time based on his start and end datetime.

I have two worksheets.

Worksheet1 displayes the start and end time of drivers in datetime:

Driver   | Start Time       | End Time
------------------------------------------
Driver 1 | 25-05-2015 09:00 | 25-05-2015 15:00
Driver 2 | 25-05-2015 15:00 | 25-05-2015 21:00
Driver 2 | 26-05-2015 09:00 | 26-05-2015 15:00
Driver 1 | 26-05-2015 12:00 | 26-05-2015 17:00

Worksheet2 displays the start date of a tour

Tour 1 | 25-05-2015 11:00
Tour 2 | 25-05-2015 16:00
Tour 3 | 25-05-2015 17:00
Tour 4 | 26-05-2015 09:00

I would like to query in worksheet 2 which driver from worksheet 1 is available during the time of the tour start.

=QUERY(Worksheet1!A:C,"select A where C <= date '"&text(A2,"yyyy-MM-dd")&"'",0)

But I understand that I will need to be working with timedate instead (to get the actual times of the start and end of the shifts) and I will need to query an interval.

Upvotes: 1

Views: 1342

Answers (1)

user3717023
user3717023

Reputation:

If you want to use dates and times, you need datetime instead of date. And since there is an interval, another comparison should be made with column B. The query string, shown with linebreaks for readability, would be

"select A 
   where B <= datetime '" & text(A2, "yyyy-MM-dd hh:mm:ss") & "' 
     and C >= datetime '" & text(A2, "yyyy-MM-dd hh:mm:ss") & "'"

This ensures that the datetime in A2 is between the datetimes in columns B and C.

Upvotes: 2

Related Questions