Adding workdays based on date column AND specific criteria from another column

jmo

Hi team - I have a a sheet that has a column for Annual and Semiannual reports. I'd like to create a date in the Next Review Date column based on adding 330 days to the Current Review Date where the Doc Gov Document Revision Schedule entry is Annually (update every 12 months based on anniversary date) and add 150 days to the Current Review Date where the Doc Gov Document Revision Schedule entry is Semiannually (update every 6 months based on anniversary date).

I tried to brute force a couple of formulas into the Next Review Date but both failed miserably.

=IFERROR(IF([Doc Gov Document Revision Schedule]@row, "Annually (update every 12 months based on anniversary date)", IF([Doc Gov Document Revision Schedule]@row, "Semiannually (update every 6 months based on anniversary date)", WORKDAY([Current Review Date]@row, 330), WORKDAY([Current Review Date]@row, 150), "")))

=IFERROR(IF([Document Revision Schedule]@row, "Annually (update every 12 months based on anniversary date)", WORKDAY([Current Review Date]@row, 330), IF([Document Revision Schedule]@row, "Semiannually (update every 6 months based on anniversary date)", WORKDAY([Current Review Date]@row, 150), "")))

Any help would be greatly appreciated.

Frank S.

Hi @jmo

Here is the formula using the names of the columns you provided. I changed the column names in my testing:

=IFERROR(IF(ISBLANK([Doc Gov Document Revision Schedule]@row), "", IF([Doc Gov Document Revision Schedule]@row = "Annually (update every 12 months based on anniversary date)", (WORKDAY([Current Review Date]@row, 330)), (WORKDAY([Current Review Date]@row, 150)))), "")

Let me know if that works for the columns you have or if you are all set.

Thanks,

Frank

Frank S.

@jmo

Please give this a try:

=IFERROR(IF(ISBLANK([Revision Schedule]@row), "", IF([Revision Schedule]@row = "Annually (update every 12 months based on anniversary date)", (WORKDAY([Current Review Date]@row, 330)), (WORKDAY([Current Review Date]@row, 150)))), "")

I only check on one condition, since you only listed 2. If the revision schedule field is blank, no check occurs.

Also, the WORKDAY function does not take into account holidays, but you can add them.

jmo

Hi @Frank S. - I keep getting an unparsable error when I copy/paste your formula into my sheet. I even adjusted for the revised column names I changed after submitting my question.

My wonky workaround was to create 2 new column - Next Semiannual Review and Next Annual Review and just added 130 and 260 workdays, respectively, to the Current Review Date column.

That way my notification looks for the appropriate Doc Gov Document Revision Schedule status and whether or not it's Semiannual or Annual then sends a notification on the appropriate date.

Frank S.

Hi @jmo

Here is the formula using the names of the columns you provided. I changed the column names in my testing:

=IFERROR(IF(ISBLANK([Doc Gov Document Revision Schedule]@row), "", IF([Doc Gov Document Revision Schedule]@row = "Annually (update every 12 months based on anniversary date)", (WORKDAY([Current Review Date]@row, 330)), (WORKDAY([Current Review Date]@row, 150)))), "")

Let me know if that works for the columns you have or if you are all set.

Thanks,

Frank

jmo

So @Frank S - here are the newly titled column

I plugged this in and it works!

=IFERROR(IF(ISBLANK([Document Revision Schedule]@row), "", IF([Document Revision Schedule]@row = "Annually (update every 12 months based on anniversary date)", (WORKDAY([Current Review Date]@row, 260)), (WORKDAY([Current Review Date]@row, 150)))), "")

Thanks for your help!

Frank S.

@jmo

Glad it worked out and happy to help.

Have a great day!

Frank