Replace date in returned query with text if before a certain date

Question

I have a report which comes in a format which must be altered, several columns removed, filtered by category, etc.

Column G is a date 'YYYY-MM-DD' column, for my purposes I need to change the date cell to a string 'OOD' if the date is before today however I can't seem to figure out how I should try this.

I have attempted various combinations of array formula and substitute etc but all seem to break the query.

=QUERY('test data'!1:994,"select C,G,J,K,O where F='FOOD' and C is not null order by K asc",0)

player0 player0 · Accepted Answer · 2019-06-17T21:39:20

try like this:

=ARRAYFORMULA(QUERY({'test data'!A1:F994, 
 IF('test data'!G1:G994<TODAY(), "OOD", 'test data'!G1:G994), 'test data'!H1:994}, 
 "select Col3,Col7,Col10,Col11,Col15 
  where Col6='FOOD' 
    and Col3 is not null 
  order by Col11", 0))

Replace date in returned query with text if before a certain date

1 Answers