# local subflow: Count_Business_Days
# inputs: in_loc_txt_StartDate, in_loc_txt_EndDate, in_loc_txt_WeekendDays, in_loc_lst_Holidays
# outputs: out_loc_bool_Ok, out_loc_num_BusinessDays, out_loc_num_CalendarDays, out_loc_num_WeekendDays, out_loc_num_Holidays, out_loc_txt_Error
# Counts the business days of a period, both dates included, as NETWORKDAYS.INTL does in Excel: the calendar days,
# minus the days off of the week, minus the holidays that fall on a working day. A period given backwards gives a negative count.
# One Run PowerShell script action does the whole count: about a second for one week as for thirty years, where the
# same rules written with native actions take 7 to 28 s in the designer.
# The script reads the dates as yyyy-MM-dd and compares real dates: nothing depends on the regional settings of the PC.
# Never stops the flow: a date that cannot be read comes back as out_loc_bool_Ok = False with a message in out_loc_txt_Error.
@@regionColor: '#57FFE1'
**REGION 1. Outputs initialised: every path leaves them defined
SET out_loc_bool_Ok TO False
SET out_loc_num_BusinessDays TO 0
SET out_loc_num_CalendarDays TO 0
SET out_loc_num_WeekendDays TO 0
SET out_loc_num_Holidays TO 0
SET out_loc_txt_Error TO $''''''
**ENDREGION
@@regionColor: '#C6E0B4'
**REGION 2. The four inputs travel to the script in one text
Text.JoinText.JoinWithCustomDelimiter List: in_loc_lst_Holidays CustomDelimiter: $''',''' Result=> txt_Holidays
SET txt_Values TO $'''%in_loc_txt_StartDate%|%in_loc_txt_EndDate%|%in_loc_txt_WeekendDays%|%txt_Holidays%'''
# An apostrophe would close the text inside the script: it is doubled
Text.Replace.ReplaceText Text: txt_Values TextToFind: $'''\'''' IgnoreCase: False ReplaceWith: $'''\'\'''' ActivateEscapeSequences: False ComparisonType: Text.TextComparisonType.Ordinal Result=> txt_Values
**ENDREGION
@@regionColor: '#D9C3E9'
**REGION 3. Count: one script action for the calendar days, the days off and the holidays
Scripting.RunPowershellScript.RunScript Script: $'''# Business days between two dates: one text in, one line out.
# In : start|end|days off|holidays (dates as yyyy-MM-dd, holidays separated by commas).
# Out: OK|business days|calendar days|weekend days|holidays|   or   ERROR|0|0|0|0|message
# Dates are read with the invariant culture and compared as real dates: nothing depends on the regional settings.
# ASCII only on purpose: the same text lives inside a flow and inside a PowerShell action.
$ErrorActionPreference = \'Stop\'
$inv = [System.Globalization.CultureInfo]::InvariantCulture
$none = [System.Globalization.DateTimeStyles]::None
$answer = \'ERROR|0|0|0|0|\'
try {
    $values = \'%txt_Values%\'.Split(\'|\')
    if ($values.Count -ne 4) { throw \'A value holds the character | : it separates the values given to the script\' }
    $day = [datetime]::MinValue
    if (-not [datetime]::TryParseExact($values[0], \'yyyy-MM-dd\', $inv, $none, [ref]$day)) { throw (\'Start date is not a yyyy-MM-dd date: \' + $values[0]) }
    $from = $day
    if (-not [datetime]::TryParseExact($values[1], \'yyyy-MM-dd\', $inv, $none, [ref]$day)) { throw (\'End date is not a yyyy-MM-dd date: \' + $values[1]) }
    $to = $day
    # The days off: every word must be one of the seven English day names, once
    $names = \'Monday\', \'Tuesday\', \'Wednesday\', \'Thursday\', \'Friday\', \'Saturday\', \'Sunday\'
    $off = @{}
    foreach ($word in [regex]::Matches($values[2], \'[A-Za-z]+\')) {
        $name = $names | Where-Object { $_ -eq $word.Value }
        if (-not $name -or $off.ContainsKey($name)) { throw (\'Weekend days must be English day names such as Saturday,Sunday: \' + $values[2]) }
        $off[$name] = $true
    }
    # A period given backwards is counted forwards; the sign is turned at the end
    $sign = 1
    if ($from -gt $to) { $day = $from; $from = $to; $to = $day; $sign = -1 }
    # Whole weeks are multiplied; only the days left, six at most, are visited
    $calendar = ($to - $from).Days + 1
    $weeks = [int][Math]::Floor($calendar / 7)
    $work = $weeks * (7 - $off.Count)
    for ($i = 0; $i -lt ($calendar - 7 * $weeks); $i++) {
        if (-not $off.ContainsKey($from.AddDays($i).DayOfWeek.ToString())) { $work++ }
    }
    # Holidays: counted once, inside the period, and only when they fall on a working day
    $seen = @{}
    $holidays = 0
    foreach ($item in $values[3].Split(\',\')) {
        $text = $item.Trim()
        if ($text -eq \'\') { continue }
        if (-not [datetime]::TryParseExact($text, \'yyyy-MM-dd\', $inv, $none, [ref]$day)) { throw (\'A holiday is not a yyyy-MM-dd date: \' + $text) }
        if ($day -ge $from -and $day -le $to -and -not $seen.ContainsKey($text)) {
            $seen[$text] = $true
            if (-not $off.ContainsKey($day.DayOfWeek.ToString())) { $holidays++ }
        }
    }
    $answer = \'OK|\' + (($work - $holidays) * $sign) + \'|\' + $calendar + \'|\' + ($calendar - $work) + \'|\' + $holidays + \'|\'
} catch { $answer = \'ERROR|0|0|0|0|\' + $_.Exception.Message }
[Console]::Out.Write($answer)''' ScriptOutput=> txt_ScriptOutput ScriptError=> txt_ScriptError
**ENDREGION
@@regionColor: '#F4B6B6'
**REGION 4. Guards: the action never fails by itself, the script answers OK or ERROR with a message
IF txt_ScriptError <> $'''''' THEN
    Text.Trim Text: txt_ScriptError TrimOption: Text.TrimOption.Both TrimmedText=> out_loc_txt_Error
    EXIT FUNCTION
END
Text.SplitText.SplitWithDelimiter Text: txt_ScriptOutput CustomDelimiter: $'''|''' IsRegEx: False Result=> lst_Answer
IF lst_Answer.Count < 6 THEN
    SET out_loc_txt_Error TO $'''No answer from the script: %txt_ScriptOutput%'''
    EXIT FUNCTION
END
IF lst_Answer[0] <> $'''OK''' THEN
    Text.Trim Text: lst_Answer[5] TrimOption: Text.TrimOption.Both TrimmedText=> out_loc_txt_Error
    EXIT FUNCTION
END
**ENDREGION
@@regionColor: '#FFD966'
**REGION 5. Outputs: the count carries the sign of the period, the details describe it
Text.ToNumber Text: lst_Answer[1] Number=> out_loc_num_BusinessDays
Text.ToNumber Text: lst_Answer[2] Number=> out_loc_num_CalendarDays
Text.ToNumber Text: lst_Answer[3] Number=> out_loc_num_WeekendDays
Text.ToNumber Text: lst_Answer[4] Number=> out_loc_num_Holidays
SET out_loc_bool_Ok TO True
**ENDREGION