How do I set up dates to include holidays and carry from year to year?

I am setting up a task list and I don't want to manually update each year. I want to add holidays and have the dates update each year automatically or as I adjust the holiday schedule. Thank!


  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 02/16/21

    Hi @Tammy Simpson 

    Hope you are fine, you can do that from the admin center by defining the working days and no working days ( Holidays ), please read the following article Define Working Days, Non-working Days, and Holidays on a Project Sheet

    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Hi! I have so many date parameters being used such as BD 15 and 15th of the month as an example. Some of the dates need to exclude weekends/holidays while others just need to exclude holidays and include weekends. I started using a formula to set these dates that are repetitive monthly/quarterly. Here is an example:

    State Date is 1/1/21

    Report is due on 10th BD (since the 10th of January 2021 is a Sunday, the formula works to give me a due date of 1/11/21).

    The Start and due dates at the end are holidays that I typed in and are excluding.

    =WORKDAY([Start Date]14, 10, [Start Date]2:[Due Date]8)

    So, what I also need in this same column is a formula that will include the holidays like the formula above, BUT INCLUDE weekends. For instance, I have a report due on the 15th of each month. How can I have a formula that does what I have above, but also include weekends? Thanks!

  • Tim Starkey
    Tim Starkey ✭✭✭✭✭

    Sorry to tag onto an old thread, but I am just realizing that admin holiday control doesn't work like I assumed it had, so am doing a little digging to figure it out. OP said they wanted to have the dates and holidays update automatically each year. In @Bassam Khalil response, he noted the solution was to put the dates in the admin control of non-working days. Unfortunately, that either needs to be updated annually, or you need to pre-paste in the holidays for several years in advance. Litterally... unless I am doing something wrong (could be?)... but I just tested it out on one of our work plans. We have projects that span years. We put in our holidays back in 2021. If I scroll the gantt chart back it shows the holidays back in 2021, but does not show any holidays in the years since. So know I am about to update our holiday setting, and 500 projects are about to update with new holidays and push schedules out 7-8 days/year.

    Others have a work around or better solution? Or can explain what I am missing?

  • Tim Starkey
    Tim Starkey ✭✭✭✭✭

    OMG... never mind... apparently it wont update the existing sheets when I update the account holidays?

    Public Holidays — Smartsheet Community

    Sooo... it is REALLY important to have your account setting include holidays for the next several years. This should be clarified in the help pages.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!