### Functions to determine if a date is a workday (Power Query)

###### 2023-06-23    Power Query    0    113

The functions below can be used to determine if a date is a weekend date, a holiday date or a workday date.
The functions uses holidays for Norway, but you can adapt them to the holidays in your own country.

```//fxDateIsWeekend
//returns True if a date is a Saturday or Sunday
(InputDate as date) as logical =>
let
//Date.DayOfWeek returns a value between 0 and 6
IsWeekend = Date.DayOfWeek(InputDate, Day.Monday) >= 5
in
IsWeekend
```
```//fxDateIsHoliday
//returns True if a date is a Norwegian holiday
(InputDate as date) as logical =>
let
// calculate Easter Sunday
lngYear = Date.Year(InputDate),
lngGolden = Number.Mod(lngYear, 19) + 1,
lngCentury = Number.IntegerDivide(lngYear, 100) + 1,
lngLeapDayCorr = 3 * Number.IntegerDivide(lngCentury, 4) - 12,
lngMoonSync =  Number.IntegerDivide(8 * lngCentury + 5, 25) - 5,
lngSunday = Number.IntegerDivide(5 * lngYear, 4) - lngLeapDayCorr - 10,
lngEpact1 = Number.Mod(11 * lngGolden + 20 + lngMoonSync - lngLeapDayCorr, 30),
lngEpact2 = if lngEpact1 < 0 then lngEpact1 + 30 else lngEpact1,
lngEpact = if ((lngEpact2 = 25 and lngGolden > 11) or lngEpact2 = 24) then lngEpact2 + 1 else lngEpact2,
lngN1 = 44 - lngEpact,
lngN2 = if lngN1 < 21 then lngN1 + 30 else lngN1,
lngN = lngN2 + 7 - Number.Mod(lngSunday + lngN2, 7),
dtmES = if lngN <= 31 then #date(lngYear, 3, lngN) else #date(lngYear, 4, lngN - 31), // Easter Sunday
// check if InputDate is a holiday
holidays = {
{#date(lngYear, 1, 1), 1}, // 1. Nyttårsdag
{Date.AddDays(dtmES, - 3), 1}, // Skjærtorsdag
{Date.AddDays(dtmES, - 2), 1}, // Langfredag
{dtmES, 1}, // 1. Påskedag
{Date.AddDays(dtmES, 1), 1}, // 2. Påskedag
{#date(lngYear, 5, 1), 1}, // 1. Mai
{#date(lngYear, 5, 17), 1}, // 17. Mai
{Date.AddDays(dtmES, 39), 1}, // Kristi Himmelfartsdag
{Date.AddDays(dtmES, 49), 1}, // 1. Pinsedag
{Date.AddDays(dtmES, 50), 1}, // 2. Pinsedag
{#date(lngYear, 12, 25), 1}, // 1. Juledag
{#date(lngYear, 12, 26), 1}, // 2. Juledag
// {Date.AddDays(dtmES, - 4), 1}, // Onsdag før påske
// {#date(lngYear, 12, 24), 1}, // Julaften
// {#date(lngYear, 12, 31), 1}, // Nyttårsaften
{InputDate, 0} // not a holiday
},
IsHoliday = List.First(List.Select(holidays, each _{0} = InputDate)){1} > 0
in
IsHoliday
```
```//fxDateIsWorkday
//returns True if a date is a a work day (not a Saturday, Sunday or a Norwegian holiday)
(InputDate as date) as logical =>
let
// calculate Easter Sunday
lngYear = Date.Year(InputDate),
lngGolden = Number.Mod(lngYear, 19) + 1,
lngCentury = Number.IntegerDivide(lngYear, 100) + 1,
lngLeapDayCorr = 3 * Number.IntegerDivide(lngCentury, 4) - 12,
lngMoonSync =  Number.IntegerDivide(8 * lngCentury + 5, 25) - 5,
lngSunday = Number.IntegerDivide(5 * lngYear, 4) - lngLeapDayCorr - 10,
lngEpact1 = Number.Mod(11 * lngGolden + 20 + lngMoonSync - lngLeapDayCorr, 30),
lngEpact2 = if lngEpact1 < 0 then lngEpact1 + 30 else lngEpact1,
lngEpact = if ((lngEpact2 = 25 and lngGolden > 11) or lngEpact2 = 24) then lngEpact2 + 1 else lngEpact2,
lngN1 = 44 - lngEpact,
lngN2 = if lngN1 < 21 then lngN1 + 30 else lngN1,
lngN = lngN2 + 7 - Number.Mod(lngSunday + lngN2, 7),
dtmES = if lngN <= 31 then #date(lngYear, 3, lngN) else #date(lngYear, 4, lngN - 31), // Easter Sunday
// check if InputDate is a holiday
holidays = {
{#date(lngYear, 1, 1), 1}, // 1. Nyttårsdag
{Date.AddDays(dtmES, - 3), 1}, // Skjærtorsdag
{Date.AddDays(dtmES, - 2), 1}, // Langfredag
{dtmES, 1}, // 1. Påskedag
{Date.AddDays(dtmES, 1), 1}, // 2. Påskedag
{#date(lngYear, 5, 1), 1}, // 1. Mai
{#date(lngYear, 5, 17), 1}, // 17. Mai
{Date.AddDays(dtmES, 39), 1}, // Kristi Himmelfartsdag
{Date.AddDays(dtmES, 49), 1}, // 1. Pinsedag
{Date.AddDays(dtmES, 50), 1}, // 2. Pinsedag
{#date(lngYear, 12, 25), 1}, // 1. Juledag
{#date(lngYear, 12, 26), 1}, // 2. Juledag
// {Date.AddDays(dtmES, - 4), 1}, // Onsdag før påske
// {#date(lngYear, 12, 24), 1}, // Julaften
// {#date(lngYear, 12, 31), 1}, // Nyttårsaften
{InputDate, 0} // not a holiday
},
IsHoliday = List.First(List.Select(holidays, each _{0} = InputDate)){1} > 0,
//Date.DayOfWeek returns a value between 0 and 6
IsWeekend = Date.DayOfWeek(InputDate, Day.Monday) >= 5,
IsWorkday = not (IsWeekend or IsHoliday) // determine if InputDate is a workday
in
IsWorkday
```