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

 2023-06-23    Power Query    0    257

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
//you can adapt this to the holidays in your own country
(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)
//you can adapt this to the holidays in your own country
(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
See also how to calculate holidays with VBA.