Here is one approach to creating a holiday booking workflow that excludes weekends and bank holidays. The logic of this workflow is based on creating a collection variable containing the date range of the requested holiday dates, a collection containing the bank holidays and then checking these dates against each other to filter out the bank holidays. The key to this working is enforcing the date formats throughout the workflow.

STEP 1: Create a custom list called HolidayBooking with 2 date columns for DateStart and DateEnd. The end user will use the DateStart and DateEnd to define the duration of their holiday.

STEP 2: Create a list workflow on the HolidayBooking list with the following variables:

STEP 3: Create a collection called varColBankHol containing the bank holiday dates. These dates will eventually be excluded from the requested date range.

• Regular Expression:
  • Pattern: ;
  • Operation: Split
  • Input text: Add bank holiday dates separated with semi-colons ";". Ensure there is no semi-colon at the end of the string as per screenshot below.]
  • Store results in: varColBankHol

STEP 4: Add the start date and end date as variables.

• Set variable: varDateStart = StartDate
• Set variable: varDateEnd = EndDate

STEP 5: Create a collection called varColRequestedDates containing the range of requested holiday dates ensuring they are formatted as dd/MM/yyyy. After the loop add the end date to the varColRequestedDates collection.

• Loop: until varDateStart = varDateEnd
• Collection operation:
  • Target collection: varColRequestedDates
  • Action: Add
  • Value: fn-FormatDate(varDateStart, "dd/MM/yyyy")
• Calculate date: varDateStart + 1 day
• Collection operation:
  • Target collection: varColRequestedDates
  • Action: Add
  • Value: fn-FormatDate(varDateEnd, "dd/MM/yyyy")

STEP 6: Remove weekends from the varColRequestedDates collection of requested dates.

• For each:
  • Target collection: varColRequestedDates
  • Store results in: varDate1
• Run if:
  • Condition: If any value equals value

Value: fn-FormatDate(varDate1, "dddd")

does not begin with

S

• Collection operation:
  • Target collection: varColBusDates
  • Action: Add
  • Value: fn-FormatDate(varDate1, "dd/MM/yyyy")

STEP 7: Now we have a collection variable containing a range of dates that excludes weekends. We now need to exclude bank holidays.

• For each:
  • Target collection: varColBusDates
  • Store results in: varDate1
  • Stop processing: varYNEndLoop
• Collection operation:
  • Target collection: varColBankHol
  • Action: Exists
  • Value: fn-FormatDate(varDate1, "dd/MM/yyyy")
  • Store results in: varYNExists
• Run if:
  • Condition: If any value equals value

Workflow Data: varYNExists

equals

Value: Yes

• Collection operation:
  • Target collection: varColBusDates
  • Action: Remove by value
  • Value: fn-FormatDate(varDate1, "dd/MM/yyyy")
• Run if:
  • Condition: If any value equals value

Value: fn-FormatDate(varDate1, "dd/MM/yyyy")

equals

Value: fn-FormatDate(varDateEnd, "dd/MM/yyyy")

• Set variable: varYNEndLoop = Yes

We now have a collection of dates based on a date range specified by the user that excludes weekends and bank holidays.

Mitch