I have a table consisting of Billed Date & Payment Terms like this. The TODAY
cell is just a reference:
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")&"'")