Open Balance Aging Columns

EricB
EricB ✭✭
edited 10/17/22 in Formulas and Functions

New smart sheets user here. Can anyone point me in the right direction to have a aging column on this accounts receivable sheet that shows how long from the invoiced date it has been? Exp..1-30 days, 30-60 days, 60-90 days and so on?

Then I need it to also know when the open balance goes to $0 so it’s not showing something is past due that has been paid Thanks in advanced! You guys have been so helpful!

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @EricB

    You can use a formula to return how many days have passed between two dates, like so:

    =[Date 1]@row - [Date 2]@row

    See: Use Formulas to Perform Calculations With Dates

    In your case, you'll want to see how many days are between today and your Painting Invoice Date column. There's a function in Smartsheet called TODAY() which can create this calculation for you. See: TODAY Function


    Try adding this as a column formula in your source sheet in a new column:

    =IF(OR([Painting Invoice Date]@row = "", [Open Balance]@row = 0), "", TODAY() - [Painting Invoice Date]@row)

    This will return a blank cell if the date cell is blank OR if the Open Balance is 0. Otherwise, it will subtract the date from Today, leaving you with a number.

    You could even add the text "days" to the end of this if you'd like:

    =IF(OR([Painting Invoice Date]@row = "", [Open Balance]@row = 0), "", TODAY() [Painting Invoice Date]@row) + " days")

    See: Use column formulas to apply calculations to all rows in a sheet

    Let me know if this makes sense and works for you!

    Cheers,

    Genevieve

    Join us for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.

Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @EricB

    You can use a formula to return how many days have passed between two dates, like so:

    =[Date 1]@row - [Date 2]@row

    See: Use Formulas to Perform Calculations With Dates

    In your case, you'll want to see how many days are between today and your Painting Invoice Date column. There's a function in Smartsheet called TODAY() which can create this calculation for you. See: TODAY Function


    Try adding this as a column formula in your source sheet in a new column:

    =IF(OR([Painting Invoice Date]@row = "", [Open Balance]@row = 0), "", TODAY() - [Painting Invoice Date]@row)

    This will return a blank cell if the date cell is blank OR if the Open Balance is 0. Otherwise, it will subtract the date from Today, leaving you with a number.

    You could even add the text "days" to the end of this if you'd like:

    =IF(OR([Painting Invoice Date]@row = "", [Open Balance]@row = 0), "", TODAY() [Painting Invoice Date]@row) + " days")

    See: Use column formulas to apply calculations to all rows in a sheet

    Let me know if this makes sense and works for you!

    Cheers,

    Genevieve

    Join us for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!