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

Options
jmo
jmo ✭✭✭✭✭✭
edited 10/17/22 in Formulas and Functions

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.

Best Answer

  • Frank S.
    Frank S. ✭✭✭✭✭✭
    Answer ✓
    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. 😎

Answers

  • Frank S.
    Frank S. ✭✭✭✭✭✭
    Options

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

    Frank Smith, PMP

    Assistant Director | IT Special Projects Mgr.

    Oregon Parks & Recreation Department

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

  • jmo
    jmo ✭✭✭✭✭✭
    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.

  • Frank S.
    Frank S. ✭✭✭✭✭✭
    Answer ✓
    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. 😎

  • jmo
    jmo ✭✭✭✭✭✭
    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)))), "")

    Thanks for your help!

  • Frank S.
    Frank S. ✭✭✭✭✭✭
    Options

    @jmo

    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!