# Calculate 30 days forward, excluding weekends and holidays

One user wants to have a column, which will be a Date 30 days forward, but when if it falls on a weekend or holiday, it has to be after that. For example, today is 11/25, so after 30 days, it will be 12/25, so it should be 12/27. In the column. I know the function `WORKDAY` exists, but it counts only just workdays, not every day...

I try to use order of holiday days with function `WEEKDAY` and `IF`, but the function is very very long. In MS Excel, there can I use `COUNTIF`, but in SP I don't know how.

Here is my solution. I'm from Czech Republic, so there is our holiday's and for easterday now it calculates to year 2022.

``````=if(or(text(date+30,"d.m.")="1.1.",text(date+30,"d.m.yyyy")="17.4.2017",text(date+30,"d.m.yyyy")="2.4.2018",text(date+30,"d.m.yyyy")="22.4.2019",text(date+30,"d.m.yyyy")="13.4.2020",text(date+30,"d.m.yyyy")="5.4.2021",text(date+30,"d.m.yyyy")="18.4.2022",text(date+30,"d.m.")="1.5.",text(date+30,"d.m.")="8.5.",text(date+30,"d.m.")="6.7.",text(date+30,"d.m.")="28.9.",text(date+30,"d.m.")="28.10.",text(date+30,"d.m.")="17.11.",text(date+30,"d.m.")="26.12."),if(weekday(date+31,2)=6,date+33,if(weekday(date+31,2)=7,date+32,date+31)),if(or(text(date+30,"d.m.")="5.7.",text(date+30,"d.m.")="25.12."),if(weekday(date+32,2)=6,date+34,if(weekday(date+32,2)=7,date+33,date+32)),if(text(date+30,"d.m.")="24.12.",if(weekday(date+33,2)=6,date+35,if(weekday(date+33,2)=7,date+34,date+33)),if(or(text(date+30,"d.m.yyyy")="14.4.2017",text(date+30,"d.m.yyyy")="30.3.2018",text(date+30,"d.m.yyyy")="19.4.2019",text(date+30,"d.m.yyyy")="10.4.2020",text(date+30,"d.m.yyyy")="2.4.2020",text(date+30,"d.m.yyyy")="15.4.2022"),date+34,if(weekday(date+30,2)=6,date+32,if(weekday(date+30,2)=7,date+31,date+30))))))
``````

I know this is an old topic, but there may be people out there still using SharePoint 2013 Foundation with SP workflow 2010 like me, hope this workaround helps. It uses 2 lists and workflows to create a loop until it finds the date that does not fall on weekend or holiday. The advantage is that user can easily maintain their holidays without using hard coded calculated column.

Scenario: Find the next working day by excluding weekends & holidays.

1. Create 2 lists. One is the main list and the other for checking holidays.

Main list:

• CurrentDateTime (Date & Time) - format Date Only

• NextReminderDate (Date & Time) - format Date Only

• Workflow (Choice) - with 3 options – 1,2,3, default value 1

• DaysToSkip (Number) – default 0 and no decimal point

• IsWeekend (Calculated) – Formula=

IF(OR(WEEKDAY(CalcNextDay)=1,WEEKDAY(CalcNextDay)=7),"Yes","No") Value will be “Yes” if CalcNextDay falls on weekend

• CalcNextDay (Calculated) – Formula=

CurrentDateTime+DaysToSkip

Holiday list:

• Create a Date & Time column

1. Create 2 separate workflows in SP Designer for main list. Second workflow will be triggered to restart first workflow from top.

1. Workflow# 1 contains an IF or ELSE condition:
• The first condition will be triggered when item is created, since default value is 1.
• Add 1 day to current DaysToSkip (originally 0).
• Trigger Workflow# 2 to check if CalcNextDay falls on weekend or holiday.

1. Workflow 2 contains 1 parallel block with 2 steps.
• If CalcNextDay equals Holiday:Date

• Or if IsWeekend calculated value is “Yes”
• Workflow 1 will be triggered again to add 1 day to skip.
• This process repeats until both conditions are no longer met, and the next step “Working” is triggered to call first workflow, set NextReminderDate to CalcNextDay and stop the workflows.

1. Example: