Formula troubles.

MichelleN

I am trying to rewrite a formula and I need some help. It is to update a due date for recertification. The midyear criterion makes it more complex. Here are the parameters that reference other columns: the person's original certification date, and their 4 year recertification cycle. I am trying to get a column to autofill when their next recertification is due.

IF ([22-26recertification]@row) is BLANK

AND if the ([18-22recertification]@row (is not blank and) has a MONTH<8

THEN enter "July 31, " + (YEAR from [18-22recertification]@row + 4)

If the ([18-22recertification]@row (is not blank and) has a MONTH>=8

THEN enter "July 31, " + (YEAR from [18-22recertification]@row + 5)

IF ([18-22recertification]@row (is BLANK), AND the

(MONTH[original certification year]@row <8)

Then enter "July 31, " + (YEAR[original certification year]@row +4)

IF(MONTH [original certification year]@row>=8)

Then enter "July 31, " + (YEAR[original certification year]@row +4)

OTHERWISE

If ([22-26recertification]@row) is NOT BLANK,

AND the MONTH([22-26recertification]@row) <8

THEN enter "July 31, " + (YEAR ([22-26recertification]@row) +4)

IF the MONTH([22-26recertification]@row) >=8

THEN enter "July 31, " + (YEAR ([22-26recertification]@row) +5)

Can I put both scenarios for the first condition (if 22-26 Recertification IS blank, otherwise if it is not blank...) in the same equation with all the added conditions? I've tried to write this and get it to work up to adding that condition to it. I can't figure out from the documentation how to get that part to work. Alternately should I try doing it some other way than formula? I have also assigned them each a cohort number based on the yearly cycle they are on within the four years (so four cohorts). Could I use that somehow to simplify this? I'm going to try that next. Meantime, thanks for any help you can offer.

Kelly Moore

Hey @MichelleN

I added the IFERROR as date functions often cause errors when encountering blank or non-date values.

=IFERROR(IF(AND(IFERROR(ISDATE([22-26recertification]@row), 0), IFERROR(MONTH([22-26recertification]@row), 0) < 8), DATE(YEAR([22-26recertification]@row) + 4, 7, 31), IF(AND(IFERROR(ISDATE([22-26recertification]@row), 0), IFERROR(MONTH([22-26recertification]@row), 0) >= 8), DATE(YEAR([22-26recertification]@row) + 5, 7, 31), IF(AND(IFERROR(ISDATE([18-22recertification]@row), 0), IFERROR(MONTH([18-22recertification]@row), 0) < 8), DATE(YEAR([18-22recertification]@row) + 4, 7, 31), IF(AND(IFERROR(ISDATE([18-22recertification]@row), 0), IFERROR(MONTH([18-22recertification]@row), 0) >= 8), DATE(YEAR([18-22recertification]@row) + 5, 7, 31), IF(AND([18-22recertification]@row = "", IFERROR(MONTH([original certification year]@row), 0) < 8), DATE(YEAR([original certification year]@row) + 4, 7, 31), IF(AND([18-22recertification]@row = "", IFERROR(MONTH([original certification year]@row), 0) >= 8), DATE(YEAR([original certification year]@row) + 5, 7, 31))))))), "")

Does this work for you?

Kelly

Brent Wilson

Tried to simplify the logic a bit and came up with this, unlike @Kelly Moore it does not have an iferror which is a good idea.

I have not error checked it

=IF( ISBLANK([[22-26recertification]@row]), IF([Month]<8, IF( [Month]<8,  DATE(YEAR([ORIGINAL CERTIFICATION YEAR]@row) + 4, 6, 31),  DATE(YEAR([ORIGINAL CERTIFICATION YEAR]@row) + 5, 6, 31)), IF([Month]<8, DATE(YEAR([18-22RECERTIFICATION]@row) + 4, 6, 31), DATE(YEAR([18-22RECERTIFICATION]@row) + 5, 6, 31))), IF( [Month]<8,  DATE(YEAR([22-26recertification]@row) + 4, 6, 31),  DATE(YEAR([22-26recertification]@row) + 5, 6, 31)))

MichelleN

Thank you all for your responses! I ended up breaking this into pieces and using automated workflows to accomplish most of what I wanted. I appreciate the suggestions, which I tried. I have noticed that when there are multiple workflow automations in a row, it does not fill in cells as immediately as the formulas do. For this reason, I am considering backing up and moving one item I shifted into an automation workflow back into a formula.

Right now, I use formulas to separate the original certification date (let's call it CERT here) into two columns that pull the date and the year. Then I use the month and year columns to assign a cohort number. Since the years from from 8/1/YEAR-7/31/YEAR, and there are 4 years in a cycle, I assign each year a number. So If YEAR=2016, Month<8, cohort = 1. If YEAR = 2016, Month>7 or YEAR=2017, Month <8, Cohort = 2.

My question is this: is there a way to write the equation so I make it perpetual by taking every 4th year from the first year (e.g., 2016 + n*4 where n is any number) so I don't have to include every year in the equation? I don't see an option like this in the formulas but maybe I"m missing something. Thanks!