Search code examples
dategoogle-sheetssummatchgoogle-query-language

Query function to contain date operations [DATE + X number of months]

I am querying a Google spreadsheet, using a relatively simple expression:

=QUERY(Sheet1!A1:J200, "Select A, J", 1)

This query produces list of Offices and Last N date in columns L and M - see picture below.

What I do next is

  1. add 6 months to each of the Last N dates;
=IF(M2="","",DATE(YEAR(M2)+0,MONTH(M2)+6,DAY(M2)+0))
  1. See if any of the resultant dates are equal to or greater than TODAY();
  2. If YES, place "ALARM" into column O which is then used as a marker elsewhere, by filtering the rows with this value as an identifier.
=IF(today()>=X2,"ALARM","")

I was wondering if it is possible to create a query where 6 months would already be added to values in Column J and, possibly, the resultant list filtered IF value[i] in column J is greater than or equal to TODAY(). By achiving this, the column J would contain only Last N dates + 6 months AND >= TODAY(); All examples I have checked seems to operate with dates as filters.

enter image description here

Solution 
  • =QUERY({Sheet1!A1:A,
 ARRAYFORMULA(DATE(YEAR(Sheet1!J1:J), MONTH(Sheet1!J1:J)+6, DAY(Sheet1!J1:J)))}, 
 "select Col1,Col2,'ALARM' 
  where Col1 is not null 
    and Col2 >=date '"&TEXT(TODAY(), "yyyy-mm-dd")&"' 
  label Col2'ABCD', 'ALARM''alarm'
  format Col2 'dd-mmm-yyyy'", 1)

    0