Monday, October 13, 2014

yogi_Pull Records From Sheet Named Plan With Date And Time In Column A Greater Than Or Equal To Today's Date

                  Google Spreadsheet   Post  #1791
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI   Oct-13-2014
post by  Matteo Cadeddu:
how to select only rows with date after today with importrange?
'm working on this spreadsheet and I'd like to copy the content of the rows of the main sheet to the second sheet ONLY WHEN the date in column A is after today.

I tryed 
=query(ImportRange("14MsGbj5Ts-ZI5nqS_imk7edsHPFwcgLnHCZPsKBdyrs";"Plan!A2:C100") ; "select* where Col1 > date '"&text(A2,"dd-mm-yyyy")&"' ")
=Query(ImportRange("14MsGbj5Ts-ZI5nqS_imk7edsHPFwcgLnHCZPsKBdyrs","Plan1!A:J"), "SELECT* WHERE Col1 > toDate(NOW())")

and others but I really don't understand why it continues to give me back errors!

Could you please help me?
Thanks a lot!!

Ok guys!
This one is the one working (the one made by Anand gives also the data from days before today):
=query(Plan!A2:C100;"select* where A>=date '"&text(today();"yyyy-mm-dd")&"' ")

Now I have a new problem: i want the query to give me also hour and minutes (i edited the original sheet) in "destination" sheet. 

I think that 

=query(Plan!A2:C100;"select* where A>=date '"&text(today();"yyyy-mm-dd hh:mm")&"' ")
is not the right one because it gives me "#VALUE!"

I know that i can easily face this by formatting the date in "destination" sheet as "dd-mm-yyyy hh:mm" and this is working (but to make it work I've to let the "plan" sheet as "dd-mm-yyyy" so I can't decide the hour and minutes)... but the problem is that then the written format of the cells doesn't give what I need:
I need that the "destination" sheet to be like this in the final exportation in ".csv"
dd/mm/yyyy hh:mm, text, text

So, to make it clearer:
In the first sheet I'm formatting the date as "dd-mm-yyyy hh:mm" because I need to decide also hours and minutes. Then I would make the query to copy the data in the same format, to be exported in CSV without any changes.

Could you help me?
Guys, thanks a lot... This is making me crazy!


because the records are being pulled from a sheet within the same spreadsheet ... there is no need to use the ImportRange function