Help

IF Statement blues....

Topic Labels: Formulas
Solved
Jump to Solution
382 5
cancel
Showing results for 
Search instead for 
Did you mean: 
younger3
5 - Automation Enthusiast
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'))

 

younger3_1-1713970576050.png

 


 

1 Solution

Accepted Solutions
Kyrstin_Graves
7 - App Architect
7 - App Architect

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

See Solution in Thread

5 Replies 5
Dan_Montoya
Community Manager
Community Manager

Here is an example.

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

Kyrstin_Graves
7 - App Architect
7 - App Architect

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

Dan_Montoya
Community Manager
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"))))

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