Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- IF Statement blues....

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Topic Labels:
Formulas

Solved

Jump to Solution

0
1619
5

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- 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.

1 Solution

Accepted Solutions

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 24, 2024 09:44 AM

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

)

)

)

)

Reply

5 Replies 5

- 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.

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 24, 2024 09:44 AM

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

)

)

)

)

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 24, 2024 09:44 AM

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

- 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. 🙂

Reply