Nested IF state referencing a formula date field.

[Alex_Lerma1]

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

[dilipborad]

Hello @Alex_Lerma1 
Your formula calculation is perfect. 
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.
👍

[dilipborad]

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.

[Alexey_Gusev · Accepted solution]

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

[Alex_Lerma1]

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.

[Alex_Lerma1]

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.

[Mariane_Marketi]

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!

[dilipborad]

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