Search code examples
google-sheetsimportspreadsheetgoogle-query-languagetextjoin

Datetime comparison query

I have an importrange array to get data from alot of sheets. I organise zhem into one list and then make calculations from this list.

WORKBOOK https://docs.google.com/spreadsheets/d/1OH_LF9r04rRb1ZMuc26CwIq3NQ-qWVlb8mXJwuTechk/edit#gid=28668687

SOURCE WORKBOOK (Tracker) https://docs.google.com/spreadsheets/d/1huVGusrmhZ60zy9pg59PKN_yfL1XulwvLW5EWwHguvA/edit#gid=2007038591

sheet MODTANEWBIE_QUERY

formula #1 (add UID to DATE):

=IF($B$2="No","",ARRAYFORMULA({MODTANEWBIE_PER!A$3&"@"&QUERY(IMPORTRANGE(MODTANEWBIE_PER!$C$3,"Tracker!"&B$3&":"&B$4&MODTANEWBIE_PER!$D$3),"select Col1 WHERE Col1 = "&A2)}))

formula #2 (get data from specific column in #Tracker based on date)

=IF($B$2="No","",ARRAYFORMULA({QUERY(IMPORTRANGE(MODTANEWBIE_PER!$C$3,"Tracker!"&B$3&":"&B$4&MODTANEWBIE_PER!$D$3),"select Col1 WHERE Col1 = "&A2)}))

For some reason I an unable to query the importrange's Date that corresponds to the cell A2 in _QUERY sheet.

Please am I missing something silly? Is there a format problem?

Thanks alot for any help!

Solution

  • The formula should be:

    =IF($B$2="No",,ARRAYFORMULA(MODTANEWBIE_PER!A$3&"@"&QUERY(IMPORTRANGE(MODTANEWBIE_PER!$C$3,"Tracker!"&B$3&":"&B$4&MODTANEWBIE_PER!$D$3),"select Col1 WHERE Col2 = date '"&TEXT(A2, "e-m-d")&"'")))

    enter image description here

    However:

    • tracker sheet does not contain today's date so A2 needs to be a valid date
    • dates on tracker sheet are in column B so B4 can't be A
    • query will then look for where Col2 where dates are
    • and query is picky on dates if they are not in this format: yyyy-mm-dd