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 IsWorkdaySee also how to calculate holidays with VBA.