How to add a column in query that adds 1 year to date column

Question

I have a Google sheets with a bunch of sheets for each vehicle type. Each sheet is formatted the exact same: column A is the unit number, column B is the inspection date, and column C is the status (Active or Inactive). I currently have a working query formula that pulls all the units in the entire workbook that have inspections which expire between 395 and 335 days ago (each inspection lasts 1 year). It also outputs the sheet name where it came from (more or less).

The problem I'm running into is that instead of pulling the inspection date, I want to add 1 year to the inspection date and then show that date (the date of expiration). How can I accomplish this? I have tried a bunch of different methods, but none seem to work. I've talked with ChatGPT and it isn't very helpful either.

So essentially, I need the output to be this: column 1 is sheet name where unit was pulled from, column 2 is unit #, column 3 is inspection expiration date.

=QUERY({
ARRAYFORMULA(IF(LEN(Trucks!A1:A),"Truck",)),Trucks!A1:C;
ARRAYFORMULA(IF(LEN(Flatbeds!A1:A),"Flatbed",)),Flatbeds!A1:C;
ARRAYFORMULA(IF(LEN('Transport Trailers'!A1:A),"Transport Trailer",)),'Transport Trailers'!A1:C;
ARRAYFORMULA(IF(LEN('End Dumps'!A1:A),"End Dump",)),'End Dumps'!A1:C;
ARRAYFORMULA(IF(LEN('Side Dumps'!A1:A),"Side Dump",)),'Side Dumps'!A1:C;
ARRAYFORMULA(IF(LEN('Alum. Belly Dumps'!A1:A),"Alum. Belly Dump",)),'Alum. Belly Dumps'!A1:C;
ARRAYFORMULA(IF(LEN('Steel Belly Dumps'!A1:A),"Steel Belly Dump",)),'Steel Belly Dumps'!A1:C;
ARRAYFORMULA(IF(LEN('Dump Pups'!A1:A),"Dump Pup",)),'Dump Pups'!A1:C;
ARRAYFORMULA(IF(LEN('Light Vehicles'!A1:A),"Light Vehicle",)),'Light Vehicles'!A1:C},
"Select Col1, Col2, Col3 where Col1 is not null AND Col4 = 'Active' AND Col3 >= DATE '"&TEXT(TODAY()-395, "yyyy-mm-dd")&"' AND Col3 <= DATE '"&TEXT(TODAY()-335, "yyyy-mm-dd")&"' order by Col3 ASC",0)

Answer 1

Two approaches

In both approaches, the LET function is used to enable storing intermediary calculations in named variables for later reuse.

LAMBDA functions allow using arbitrary names in formulas as placeholders for input values. In this way, the same formula can be applied to multiple sets of input values, instead of rewriting the formula for each set.

The LAMBDA functions expect a pair input values: The first being a text string ("Truck", "Flatbed", etc.) stored in t and the second is its paired range (Trucks!A1:C, Flatbeds!A1:C, etc.) stored in r

Extra line breaks, spacing, and indentation are included only for legibility and do not affect the functioning of the code. You may keep or remove them as you prefer.

Using QUERY

=LET(
 data, LAMBDA(t, r, ARRAYFORMULA(
   { IFERROR(INDEX(r,, 1)/0, t), r,
     EDATE(INDEX(r,, 2), 12) })),

 out,{
   data("Truck", Trucks!A1:C);
   data("Flatbed", Flatbeds!A1:C);
   data("Transport Trailer", 'Transport Trailers'!A1:C);
   data("End Dump", 'End Dumps'!A1:C);
   data("Side Dump", 'Side Dumps'!A1:C);
   data("Alum. Belly Dump", 'Alum. Belly Dumps'!A1:C);
   data("Steel Belly Dump", 'Steel Belly Dumps'!A1:C) },
  
 QUERY(
   out, "Select Col1, Col2, Col5 where Col2 is not null AND
   Col4 = 'Active' AND Col5 >= DATE '"&TEXT(TODAY()-30,
   "yyyy-mm-dd")&"' AND Col5 <= DATE '"&TEXT(TODAY()+30,
   "yyyy-mm-dd")&"' order by Col5 ASC",0))

Explanation

1. LAMBDA's formula returns a 5-column array:
   1. The first column is an array of t. It's created by applying IFERROR to an array of errors and using t as its value_if_error argument:
      • An initial (helper) column of values is generated using INDEX to return the first column of r. A helper column could also be created using approaches like SEQUENCE(ROWS(r)) but INDEX(r,,1) is simply a shorter formula.
      • Each value in the column is forced to an error by dividing them by zero, and the resulting #DIV/0! and #NAME? errors are replaced by IFERROR with t
   2. The second through fourth columns are simply populated with all three columns in r
   3. The fifth column is made up of expiry dates generated by applying EDATE to add 12 months to each date in r, EDATE(INDEX(rng,,2),12)
2. out is an array that returns the stacked results of each call to data, with each call following the same syntax data(t,r)
3. Lastly, out is passed to QUERY as the data argument, which in turn returns the appropriate rows from columns 1, 2, and 5

Using FILTER

The key difference with FILTER is that most of the logic is now included in the LAMBDA's formula.

=LET(
 data, LAMBDA(t, r, 
   ARRAYFORMULA(LET(x, EDATE(INDEX(r,, 2), 12),
     { IFERROR(x/0, t), INDEX(r,,1), 
       IF((INDEX(r,, 3)="Active")+(x<=TODAY()+30)+
         (x>=TODAY()-30)=3, x,) }))),
 
 out,{
   data("Truck", Trucks!A1:C);
   data("Flatbed", Flatbeds!A1:C);
   data("Transport Trailer", 'Transport Trailers'!A1:C);
   data("End Dump", 'End Dumps'!A1:C);
   data("Side Dump", 'Side Dumps'!A1:C);
   data("Alum. Belly Dump", 'Alum. Belly Dumps'!A1:C);
   data("Steel Belly Dump", 'Steel Belly Dumps'!A1:C) },
 
 FILTER(out, INDEX(out,, 3)<>""))

1. The LAMBDA's formula returns a 3-column array, and as was used in the fifth column of the previous approach, a column of expiry dates is generated using EDATE(INDEX(r,,2),12), but in this case, it is simply stored in the named variable x for reuse.
   1. The first column in the array is again populated by the text string t. It is created in the same way as in the QUERY approach except that the helper column used in this case is x simply because it's available and IFERROR(x/0, t) is less code than IFERROR(INDEX(r,,1)/0, t)
   2. The second column is the "Unit #" INDEX(r,,1)
   3. The third column is populated with expiry dates x but only for those rows where the following 3 tests are all TRUE:
      1. Is the row's "Status" marked "Active" INDEX(r,, 3)="Active"?
      2. Is the expiry date within the next 30 days x<=TODAY()+30?
      3. Is the expiry date within the last 30 days x>=TODAY()-30?
      • The conditions are added + together which prompts Sheets to try to coerce them to numbers, which it does successfully returning 1 where TRUE and 0 where FALSE. In this way only if all conditions are TRUE will they successfully add up to 3 and pass the IF function's test.
2. out follows the same pattern as in the previous approach
3. Lastly, out is FILTERed to remove any rows missing an expiry date FILTER(out, INDEX(out,, 3)<>"")