How can I change this formula to exclude holidays?

vao
vao ✭
edited 09/06/24 in Formulas and Functions

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

Tags:
· Share on FacebookShare on Twitter

Best Answers

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓

    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

    · Share on FacebookShare on Twitter
  • vao
    vao ✭
    edited 09/09/24 Answer ✓

    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), "")

    · Share on FacebookShare on Twitter

Answers

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!