Wednesday, July 18, 2012

yogi_Query For Dates That Are Older (Newer) Than A Specified Number of Days From Today

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #639   Jul 18, 2012     www.energyefficientbuild.com.


user doebtown said:(http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/7yp-tDajmsE)
Query for dates that are older than 10 days from today 
I have a Google Form that feeds a spreadsheet. Obviously, Column A of the spreadsheet is the Timestamp from the form. So all I want to do is set up a query on a different sheet that shows only rows where the form was submitted more than 10 days ago. So why doesn't this query work:
=query(Master!A:C, "select C where (A < (now()-10))")
It's making me crazy! 
This query works just fine:
=query(Master!A:C, "select C where (A < (now()))")
So why doesn't it work when I try to subtract 10 days?
I've seen and used queries where all sorts of machinations are necessary to convert now() to text, then back to a date to compare it to a user inputted format of a date. As shown here:
=query(Master!A:C, "select C where (A < date '"&text(now()+30,"yyyy-mm-dd")&"')")
(But even THAT operates on now()+30!)
But since the Timestamp and now() both use the exact same formats now()-10 SHOULD be all I need. I can enter =now()-10 into a blank cell and IT works just fine. Why won't it work in the query. 
I suspect it has something to do with quotes, but I've tried all sorts of iterations of it and I can't get it right.
Could somebody just please help me? Before I go crazy with this stuff?
Thank you in advance. Many times over!
-----------------------------------------------------------------------------------------------
following is a solution to a little more generalized problem



No comments:

Post a Comment