Open Balance Aging Columns

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!

Find more posts tagged with

Accepted answers

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

All comments

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

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.