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

Options
✭✭✭✭✭✭
edited 10/17/22

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.

• ✭✭✭✭✭✭
Options

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 Smith, PMP

Assistant Director | IT Special Projects Mgr.

Oregon Parks & Recreation Department

If my response helps, please mark it as an accepted answer. 😎

• ✭✭✭✭✭✭
Options

=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.

Frank Smith, PMP

Assistant Director | IT Special Projects Mgr.

Oregon Parks & Recreation Department

If my response helps, please mark it as an accepted answer. 😎

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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 Smith, PMP

Assistant Director | IT Special Projects Mgr.

Oregon Parks & Recreation Department

If my response helps, please mark it as an accepted answer. 😎

• ✭✭✭✭✭✭
edited 10/17/22
Options

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)))), "")

• ✭✭✭✭✭✭
Options

Glad it worked out and happy to help.

Have a great day!

Frank

Frank Smith, PMP

Assistant Director | IT Special Projects Mgr.

Oregon Parks & Recreation Department

If my response helps, please mark it as an accepted answer. 😎

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!