Home > Software design >  Calculate due dates inside query
Calculate due dates inside query

Time:09-15

I have a table consisting of Billed Date & Payment Terms like this. The TODAY cell is just a reference:

enter image description here

I need to use a query to select & identify which one is overdue for over 30 days. I know a solution is to create another column that calculates the Due Date from the Net and selects one that are later than 30 days compared to Today() with this formula:

=QUERY(A2:B3,"SELECT A, B WHERE A <= date '"&TEXT(TODAY()-30,"yyyy-mm-dd")&"'")

I am wondering if it is possible to calculate the Due Date inside the query without having to create a new column for it. Something like

"SELECT A WHERE A   RIGHT(B:B,2) - 30 > 30

Thank you!

CodePudding user response:

You may try something like-

=QUERY(ArrayFormula((A2:A4 INDEX(SPLIT(B2:B4," "),,2))),"where Col1 <= date '"&TEXT(TODAY()-30,"yyyy-mm-dd")&"'")

enter image description here

  • Related