# IF Statement blues....

Topic Labels: Formulas
Solved
290 5
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

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

1 Solution

Accepted Solutions
6 - Interface Innovator

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

5 Replies 5
Community Manager

Here is an example.

5 - Automation Enthusiast

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

6 - Interface Innovator

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

Community Manager
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"))))
5 - Automation Enthusiast

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