Open Balance Aging Columns

EricB

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!

Genevieve P.

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

Genevieve P.

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