Trying to capture past dates and future dates

dchavvez

So, I have a column where I can predicted dates (to pay an invoice). Some of these values are past the predicted date of payment. Therefore, I want to make a formula that will capture all those whose date is in the past and those invoices whose predicted pay day is in the next 30 days. So far,

I am only able to do :

=SUMIFS({UDA}, {UDM}, $[30 Day]$1, {UID}, $ID@row, {UTY}, $[REPORT 1 - Invoices by Category (next 30 days)]@row)

Which only adds the invoices that fall on the 30 days for the future.

Any help :D

jmyzk_cloudsmart_jp

@dchavez

You can use PredictedPayDate or UDM@row <= TODAY(30) .

=SUMIFS(UDA:UDA, UDM:UDM, <=TODAY(30), UTY:UTY, UTY#)

https://app.smartsheet.com/b/publish?EQBCT=84250de5a32c4596a7a640080475694b

Assumption

• UDA: column holding data values related to the amount of each invoice.
• UDM: column containing the due dates or payment dates of the invoices.
• UID: the unique identifier for each invoice, such as an invoice number.
• UTY: type of invoice, possibly indicating different types of transactions