Adding workdays based on date column AND specific criteria from another column
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
-
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
-
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. 😎
-
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.
-
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. 😎
-
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 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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!