How can I change this formula to exclude holidays?

vao

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

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

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!