Google Spreadsheet Querying and comparing a date with a datetime

Question:

Hi,

I'm using Google Spreadsheets on Chrome on a Windows 7 PC.  My locale is UK.

I have a spreadsheet which is populated from a form.  The first column of the spreadsheet is filled with timestamps, automatically generated when the form is submitted.  They look like this 08/10/2012 16:32:58.

On another sheet of the spreadsheet, I have a query formula, that looks like this:

=QUERY('Form data'!A1:U99,A3,1)

Where column A of 'Form data' contains the datetimes and cell A3 contains:

=CONCATENATE("select A,C,D,E,I,J,K,L,M,N,O,P,Q,R,S,T,U where A >= '",B2,"' and B contains '",LOWER(D2),"@[mydomain].co.uk'")

Where cell B2 contains a date, entered by the user, that looks like this 01/09/2012, and cell D2 contains an email address, entered by the user from a drop down list.

The email address bit works fine. But the date comparison doesn't work. The query function only seems to compare the first part of the entered date, the date of the month, with the first part of the datetime, again, the day of the month. The month and year are both ignored. So if the user enters a date of 01/04/2012, the returned results will be any date where the day of the month is greater than or equal to 1, which is of course every date. So the results are effectively unfiltered. Similarly, if the user enters the date, 29/09/2012, the results will only include lines where the day of the month if greater or equal to 29. So 30/04/2012 would be included, but 01/10/2012 would not.

I have tried several combinations of DATETIME and TIMESTAMP commands, but always get errors. Please will someone much more knowledgeable than me show me what I'm missing?

Thanks

Alec

Solution:

Try this in Cell A3:

=CONCATENATE("select A,C,D,E,I,J,K,L,M,N,O,P,Q,R,S,T,U where todate(A) >= date '",TEXT(B2,"yyyy-MM-dd"),"' and B contains '",LOWER(D2),"@[mydomain].co.uk'")

NOTE: You must format Column A of Sheet 'Form data' as date from "Format" Menu >> "Number" >> "9/26/2008 15:59:00" .

Have a look at the screenshot below:

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private or confidential projects:

http://igoogledrive.blogspot.in/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan,

iGoogleDrive