0

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.

0
0

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

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

enter image description here

Holiday list:

  • Create a Date & Time column

enter image description here


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

enter image description here enter image description here enter image description here


  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

enter image description here

  • 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:

enter image description hereenter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.