# Nested IF state referencing a formula date field.

Topic Labels: Formulas
Solved
656 7
cancel
Showing results for
Did you mean:
6 - Interface Innovator

Hello All,

I need help with a nested IF statement that references a formula date field. I'm not sure if this is causing my issues, but I can only get my formula to output two of the four values. In the formula below, "Expired" and "Missing" appear, but "Expired" is not accurate when I update the date.

What I am trying to accomplish is:
- If the date is past today, display 'Expired'.
- If it is within six months of the due date, display 'Expiring'.
- If it is greater than six months, display 'Active'.
- If no date value is in the field, display 'Missing'.

If anyone can let me know what I am doing wrong, I would appreciate it.

``````IF(
{H.I.P.S - Due Date} < TODAY(),
'Expired',
IF(
AND(
{H.I.P.S - Due Date} >= TODAY(),
{H.I.P.S - Due Date} <= DATEADD(TODAY(), 6, 'months')
),
'Expiring',
IF(
{H.I.P.S - Due Date} > DATEADD(TODAY(), 6, 'months'),
'Active',
'Missing'
)
)
)``````

1 Solution

Accepted Solutions
12 - Earth

Hi,
usually I prefer such Airtable 'emptiness check' like IF({Field}, 'value present' , 'cell empty')

``````IF({H.I.P.S - Due Date},
IF(IS_BEFORE({H.I.P.S - Due Date},TODAY()),'Expired',
IF(DATETIME_DIFF({H.I.P.S - Due Date},TODAY(),'months')<=6,
'Expiring','Active')
),
'Missing')``````

On the other hand, starting with 'guard clauses' always better for nested IFs

``````IF({H.I.P.S - Due Date}='','Missing',
IF(IS_BEFORE({H.I.P.S - Due Date},TODAY()),'Expired',
IF(DATETIME_DIFF({H.I.P.S - Due Date},TODAY(),'months')>6,
'Active','Expiring')))``````

7 Replies 7
9 - Sun

Hello @Alex_Lerma1
Just need to make a couple of changes to the conditions. There are many other ways to create it. But I've just used your version and modified it. Which makes it easy for you to understand.
For reference check the attached image.

``````IF({H.I.P.S - Due Date}!="",
IF({H.I.P.S - Due Date} < TODAY(),
'Expired',
IF(
AND(
{H.I.P.S - Due Date} >= TODAY(),
{H.I.P.S - Due Date} <= DATEADD(TODAY(), 6, 'months')
),
'Expiring',
IF(
{H.I.P.S - Due Date} > DATEADD(TODAY(), 6, 'months'),
'Active',
BLANK()
)
)
), "Missing")``````

If this is not the right solution then respond with screenshots.
👍

9 - Sun

After fixing the current one later you can also implement this  Introducing single-select output for formula field... - Airtable Community
This makes your options more colorful and you can directly use it on filters and interfaces as well.

12 - Earth

Hi,
usually I prefer such Airtable 'emptiness check' like IF({Field}, 'value present' , 'cell empty')

``````IF({H.I.P.S - Due Date},
IF(IS_BEFORE({H.I.P.S - Due Date},TODAY()),'Expired',
IF(DATETIME_DIFF({H.I.P.S - Due Date},TODAY(),'months')<=6,
'Expiring','Active')
),
'Missing')``````

On the other hand, starting with 'guard clauses' always better for nested IFs

``````IF({H.I.P.S - Due Date}='','Missing',
IF(IS_BEFORE({H.I.P.S - Due Date},TODAY()),'Expired',
IF(DATETIME_DIFF({H.I.P.S - Due Date},TODAY(),'months')>6,
'Active','Expiring')))``````

6 - Interface Innovator

Good morning @dilipborad , I appreciate your response and assistance. Your formula works correctly when I reference the date field labeled "H.I.P.S", but it encounters a problem when referencing the formula date field. This was the issue I repeatedly faced. I am thrilled about the latest single-select update. I can now save multiple automations within most of my bases.

6 - Interface Innovator

Thank you @Alexey_Gusev ! Both options provided worked. I initially thought my issue was related to referencing a formula date field, but it seems that was not the case. I appreciate the time you spent on this. I spent a few hours on it myself and couldn't seem to get it working.

6 - Interface Innovator

Today I ran into a formula issue too.

In the past, I would've done the same and come here for the solution or I would try and find a solution myself. Not today, today AI built and edited the formulas for me. It was a bliss!

9 - Sun

Hello @Mariane_Marketi
You just mentioned you've formula issues, can you give more context to it?

@Alex_Lerma1 you mentioned that the formula is not working.
Sorry about it but in your attached image your {H.I.P.S - Due Date} data type(return value) is the date. But when you use the formula you should also check the return value. Because then only you can restrict it to not getting errors.
I hope you get it.
If you already solved it. then it's 👍
Otherwise, try it on your due date formula.

``IF({H.I.P.S Date}, DATEADD({H.I.P.S Date},1, "year"), BLANK())``