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