How can I change this formula to exclude holidays?

I have set up a project status report to automate expected deadline dates for certain project deadlines. My current formula excludes the weekends, but not holidays. What can I change/add to this formula to do so?

=IFERROR(WORKDAY([CD Kickoff (E)]@row, +1), "")

Also, if I need to have the dates counted by 4 weeks vs days how might this be changed? Currently am converting 4 weeks into # of days to follow the existing formula.

(Project tracker is formatted to read left to right across columns rather than top to bottom with hierarchy rows.)

Find more posts tagged with

Formulas

Accepted answers

KPH

Hi @vao

You can add the holidays to the WORKDAY function. Either by including them within DATE functions or creating a list in a date formatted column and referencing that column.

This would exclude Jan 1, 2024

=IFERROR(WORKDAY([CD Kickoff (E)]@row, +1,DATE(2025, 1, 1)), "")

This would exclude all dates in a column called Holiday

=IFERROR(WORKDAY([CD Kickoff (E)]@row, +1,Holiday:Holiday), "")

If you put the Holidays in a separate sheet you can use a cross sheet reference instead. That would look something like

=IFERROR(WORKDAY([CD Kickoff (E)]@row, +1,{Holidays}), "")

If your worksheet is set to a 5 day working week, to convert days into weeks you divide the output by 5:

=(IFERROR(WORKDAY([CD Kickoff (E)]@row, +1,Holiday:Holiday), ""))/5

vao

Thank you all for your help! I was able to create a Holidays column within my sheet. This is my new formula:

=IFERROR(WORKDAY([CD Kickoff (E)]@row, +1, Holidays:Holidays), "")

All comments

Paul Newcome

You would use the third field within the WORKDAY function. The below linked article includes examples on how to exclude a single holiday or a range of holidays.

https://help.smartsheet.com/function/workday

KPH

Hi @vao

You can add the holidays to the WORKDAY function. Either by including them within DATE functions or creating a list in a date formatted column and referencing that column.

This would exclude Jan 1, 2024

=IFERROR(WORKDAY([CD Kickoff (E)]@row, +1,DATE(2025, 1, 1)), "")

This would exclude all dates in a column called Holiday

=IFERROR(WORKDAY([CD Kickoff (E)]@row, +1,Holiday:Holiday), "")

If you put the Holidays in a separate sheet you can use a cross sheet reference instead. That would look something like

=IFERROR(WORKDAY([CD Kickoff (E)]@row, +1,{Holidays}), "")

If your worksheet is set to a 5 day working week, to convert days into weeks you divide the output by 5:

=(IFERROR(WORKDAY([CD Kickoff (E)]@row, +1,Holiday:Holiday), ""))/5

vao

Thank you all for your help! I was able to create a Holidays column within my sheet. This is my new formula:

=IFERROR(WORKDAY([CD Kickoff (E)]@row, +1, Holidays:Holidays), "")

Paul Newcome

Happy to help. 👍️

KPH

https://community.smartsheet.com/discussion/comment/453049#Comment_453049

Looks great! Well done!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.