Help

Help combining IF and IS_BEFORE formulas

Topic Labels: Formulas
1095 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Megan_Woolston
5 - Automation Enthusiast
5 - Automation Enthusiast

Could someone help me create a formula for the following scenario? We run an accreditation system, and have a policy that went into effect on January 1, 2018 saying that programs have five years from the date of their application to submit their report to us or their eligibility will expire, but programs that applied before that policy went into effect were grandfathered in and have five years from January 1, 2018.

If it’s helpful, my application date field is in MM/DD/YYYY format, and I’d like the expiration date to generate in the same format.

If (application date field) is before 01/01/2018, (expiration date field) is 12/31/2022. If (application date field) is after 01/01/2018, (expiration date field) is 5 years after (application date field). If (application date field) is empty, (expiration date field) is blank

Thank you!

1 Reply 1
Megan_Woolston
5 - Automation Enthusiast
5 - Automation Enthusiast

I’ve got a partial formula working here that is returning the correct expiration date for those who applied on or after 01/01/2018, and returns a blank if no application date was entered. Now I just need help incorporating the other part of the equation, which is setting a 12/31/2022 expiration date for anyone who applied before 01/01/2018. I can figure out the formula for that function on its own, but I’ve tried every combination of nesting I can think of, and I’m always terrible at identifying where the parentheses/commas/quotations/etc. go.

IF(AND({Date Status Took Effect},IS_AFTER(
{Date Status Took Effect},
DATETIME_PARSE(
“12/31/2017”,
“MM/DD/YYYY”
)
)
),
DATEADD({Date Status Took Effect},5,‘y’),
BLANK()
)