Formula - 30 days/60 days Automatically

Options

Is there a formula that will calculate 30 days, 60 days, & 90 plus days for aged AR without having to change the date range each month? I don't want to change the months on each formula each month. This is what I'm using right now.

=SUMIFS([Invoice Amount]:[Invoice Amount], [Paid in Full]:[Paid in Full], "", [Project Name]:[Project Name], "Name", [Month End Date]:[Month End Date], @cell >= DATE(2023, 8, 1), [Month End Date]:[Month End Date], @cell <= DATE(2023, 9, 1))

Answers

  • Samuel Mueller
    Samuel Mueller Overachievers
    Options

    A helper column with a formula in it, then reference that column in your formula.

    someething like

    =today()+30

    =today()+60

    =today()+90

    or if you need it to update every month, you could create an automation that records a date on the first of the month in a helper column then you would have 3 extra columns:

    =[helpercolumn]@row+30

    =[helpercolumn]@row+60

    =[helpercolumn]@row+90

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!