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 Admin
    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

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!