- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Apr 24, 2024 07:57 AM
I've read through several help topics and I am still having issues, hoping someone can help me.
I have a field that is counting the days between the due date and todays date. I need to use these numbers to create a formula to provide an aging report.
I'd like the 'Aging' Field to use age count to represent
I need to group my table by aging, which I am trying to do by creating a formula based on the age count.
- if age count has a number between 1-30, aging is 1-30
- if age count has a number that falls between 31-60, aging is 31-60
- if age count has a number that falls between 61-90, aging is 61-90 Days
- if age count has a number that falls between 91-119, aging is 91-119 Days
- if age count has a number that falls between <120, aging is over 120 days
I’ve read through many articles and forum discussions about the nested formulas, but continue to run into errors, as a lot of the examples are not for so many options.
Not only am I having trouble with the formula the age count formula I currently have isn’t working properly, since anything with a $0.00 balance should not have an age count. Below is the formula I am currently using:
IF({Balance Due}='$0.00',"",DATETIME_DIFF({todays date},{Due Date},'days'))
Solved! Go to Solution.
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Apr 24, 2024 09:44 AM
For "Age Count", you should use:
IF({Balance Due} = 0, BLANK(), DATETIME_DIFF(todays date}, {Due Date}, 'days'))
For "Aging", you should use:
IF(AND({Age count} >= 1, {Age count} <= 30), '1 - 30',
IF(AND({Age count} >= 31, {Age count} <= 60), '31 - 60',
IF(AND({Age count} >= 61, {Age count} <= 90), '61 - 90',
IF(AND({Age count} >= 91, {Age count} <= 119), '91 - 119',
IF({Age count} >= 120, '>120', BLANK())
)
)
)
)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Apr 24, 2024 08:54 AM
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Apr 24, 2024 09:36 AM
Dan the formulas didn't come through with the example.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Apr 24, 2024 09:44 AM
For "Age Count", you should use:
IF({Balance Due} = 0, BLANK(), DATETIME_DIFF(todays date}, {Due Date}, 'days'))
For "Aging", you should use:
IF(AND({Age count} >= 1, {Age count} <= 30), '1 - 30',
IF(AND({Age count} >= 31, {Age count} <= 60), '31 - 60',
IF(AND({Age count} >= 61, {Age count} <= 90), '61 - 90',
IF(AND({Age count} >= 91, {Age count} <= 119), '91 - 119',
IF({Age count} >= 120, '>120', BLANK())
)
)
)
)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Apr 24, 2024 09:44 AM
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Apr 24, 2024 09:50 AM
Thank you Krystin, this worked perfect and simple enough for me to use again for future formulas. 🙂
