Google sheets query returning incorrect date

Question

I have a got a query where I'm trying to pull in the next 14 days events and previous 14 days events

For some reason, I'm getting very dates added which are in the past or way into the future

=QUERY(Sheet1!A2:H200,"select A,B,G where dateDiff(now(), G) <14 and G is not null")

ABC 1 15 Feb 2019 ABC 1 1 Nov 2018 DFG 1 11 Nov 2018 ABC 1 2 Nov 2018

Next is the previous 14 days

=QUERY(Sheet1!A:G,"select A,B,G where dateDiff(now(), G) >14 and G is not null")

   ABC 1    20 Oct 2018
   ABC 1    20 Oct 2018

I'm doing something wrong with the query

https://docs.google.com/spreadsheets/d/1WI-FS0XFGi09d2wO005S3kOV_L2s9eR3ILxST6I1nVU/edit?usp=sharing

Answer 1

If you wanted to do it all using datediff, it would be

=query(A:C,"select A,B,C where datediff(C,now())<14 and datediff(C,now())>0")

for dates in next fortnight (fourteen nights or 2 weeks) and

=query(A:C,"select A,B,C where datediff(now(),C)<14 and datediff(now(),C)>0")

for previous fortnight.

You might want to put <= and >=, depending whether you want to include today's date and date 14 days before/after today.

Answer 2

When you use less than 14, Future dates are also included because datediff returns a negative number. So, add a another condition to exclude future dates like:

=QUERY(A:C,"select A,B,C where dateDiff(now(), C) <14 and C<now() and C is not null")