Google sheets takes time to update!

The biggest problem is that you haven’t created the logic to join the date and time into one date value. That’s the part I tried to explain in my previous post.

What you have right now is 4 date columns instead of 2. So your query is looking at all 4 as individual dates and times.

Let say you make a booking for April 30th from 13:00 to 14:00. Your date only columns will have a date of April 30, but the underlying time is 00:00. Your time only column will have a date of April 26 with a time of 13:00 or 14:00. Each date column on it’s own does not contain all of the date and time information you need. Your query is comparing 4 different sets of dates and times. That’s why it doesn’t work.

You need to join the separate date and time together into one single date time value. The math column will convert your your date into a simple and predictable format of Year Month Day (YYYYMMDD). Then you join that with the time using a template column. And finally you use the Text To Date column to convert that text into an actual date time that you can then use in your query column.

There is another method that should work a lot better and be more reliable. (This only works because you already have your time columns set as Date type columns, which is good) What you would do is set up a math column like below. Date is your date column and Time is your time column. This math will add the selected time to the date, and will give you a single date column that contains both the correct date and time. (This works because the date columns are defaulting to midnight, so it’s easy to add the correct number of hours and minutes to that date.) Do this for your Start and End dates. Then change your query to only use those two math columns instead of the separate date and time columns.

Date + HOUR(Time)/24 + MINUTE(Time)/1440
2 Likes