Holiday exclusion in a Workday formula

I am trying to write a formula using the workday function that excludes certain holiday. is there a way to have it know the holiday(s) without putting in the actual date? Otherwise, every year I will have to update the formula with each holiday's new date. Here is an example of what I am currently using:

=WORKDAY([Requested End Date]@row, 1, DATE(2025, 1, 1), DATE(2025, 7, 4), DATE(2025, 5, 26), DATE(2025, 9, 1), DATE(2025, 11, 27), DATE(2025, 12, 25))

Find more posts tagged with

Accepted answers

jmyzk_cloudsmart_jp

Hi @Candace J

Defining working days, non-working days and holidays on a sheet or account level is a standard way to account for holidays, etc. (I find the method proposed by @dojones insightful, especially the way range is used.)

https://help.smartsheet.com/articles/516392-defining-working-non-working-holidays-on-a-project-sheet

I set the non-working Days in the Project Settings in the demo sheet below. That way, the WORKDAY function or End Date, based on Start Date and Duration, considers non-working days or holidays without specifying them. (As I tested, the WORKDAY function works even if I turn off the dependency.)

https://app.smartsheet.com/b/publish?EQBCT=3da59a54532c434dbaae6172d09c3873

All comments

dojones

Create a Holiday Sheet. Enter all of the Holidays in a Holiday column. Then change formula to:

=WORKDAY([Requested End Date]@row, 1, {Holidays Range 1})

Where Holidays Range 1 is referencing the column where holidays are listed in the Holiday sheet. You still have to enter the Holidays in a sheet, but it is easier than changing a formula.

jmyzk_cloudsmart_jp

Hi @Candace J

https://help.smartsheet.com/articles/516392-defining-working-non-working-holidays-on-a-project-sheet

https://app.smartsheet.com/b/publish?EQBCT=3da59a54532c434dbaae6172d09c3873

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.