Skip to main content

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'))

 

 


 

Here is an example.

Here is an example.


Dan the formulas didn't come through with the example. 

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())
)
)
)
)


IF({Age Count}<31, "Stage 1",

IF(AND({Age Count}> 30, {Age Count}<61), "Stage 4",

IF(AND({Age Count}> 60, {Age Count}<91), "Stage 3",

IF(AND({Age Count}> 90, {Age Count}<121), "Stage 4", "super old"))))

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())
)
)
)
)


Thank you Krystin, this worked perfect and simple enough for me to use again for future formulas. 🙂 

Reply


Terms & Conditions