Excel WORKDAY equivalent in Power Query and Power BI

If you want to transform your Excel calculations over to Power Query you might appreciate functions there that have the same syntax like in Excel itself. Today I’m going to share the Power Query function for the Excel WORKDAY function. It has exactly the same function parameters than its Excel equivalent.
(If you came here for the NETWORKDAYS function instead, please check out this post: Date.Networkdays function for Power Query and Power BI – (thebiccountant.com) )

Function code for Excel WORKDAY equivalent


let func =
(StartDate as date, Days as number, optional Holidays as list) =>
let
/* Debug parameters
StartDate = #date(2008, 10, 1),
Days = 151,
//Holidays = {#date(2008,11,26), #date(2008,12,4), #date(2009,1,21)},
*/
Holidays_ = if Holidays = null then 0 else List.Count(Holidays),
// Create a list of days that span the max possible period
ListOfDates =
if Days >= 0 then
List.Dates(
StartDate,
Number.RoundUp((Days + Holidays_) * (7 / 5) + 2, 0),
#duration(1, 0, 0, 0)
)
else
let
EarliestStartDate = Date.From(
Number.From(
Date.AddDays(StartDate, Number.RoundUp((Days – Holidays_) * (7 / 5) – 2, 0))
)
),
Result = List.Dates(
EarliestStartDate,
Number.From(StartDate – EarliestStartDate),
#duration(1, 0, 0, 0)
)
in
Result,
// if the optional Holidays parameter is used: Keep only those dates in the list that don't occur in the list of Holidays;
// otherwise continue with previous table
DeleteHolidays = if Holidays = null then ListOfDates else List.Difference(ListOfDates, Holidays),
// Select only the first 5 days of the week
// The 1 in the 2nd parameter of Date.DayOfWeek makes sure that Monday will be taken as first day of the week
DeleteWeekends = List.Select(DeleteHolidays, each Date.DayOfWeek(_, 1) < 5),
// Count the number of days (items in the list)
CountDays =
if Days >= 0 then
DeleteWeekends{Days}
else
DeleteWeekends{List.Count(DeleteWeekends) + Days},
// CountDays = if Days >= 0 then List.Last(DeleteHolidays) else List.First(DeleteHolidays),
Result = if CountDays = null then StartDate else CountDays
in
Result ,
documentation = [
Documentation.Name = " Xls_WORKDAY ",
Documentation.Description = " Returns a number that represents a date that is the indicated number of working days before or after a date (the starting date). ",
Documentation.LongDescription = " Returns a number that represents a date that is the indicated number of working days before or after a date (the starting date). Working days exclude weekends and any dates identified as holidays. ",
Documentation.Category = " Xls.Date ",
Documentation.Source = " www.TheBIcountant.com – https://wp.me/p6lgsG-2sW ",
Documentation.Version = " 1.0 ",
Documentation.Author = " Imke Feldmann ",
Documentation.Examples = {[Description = " ",
Code = " let
StartDate = #date(2008, 10, 1),
Days = 151,
Holidays = {#date(2008,11,26), #date(2008,12,4), #date(2009,1,21)},
Result = Xls_WORKDAY(StartDate, Days, Holidays)
in
Result ",
Result = " #date(2009,5,5)
"]}]
in
Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))

view raw

Xls.WORKDAY.pq

hosted with ❤ by GitHub

Click on “view raw” down right in the function code above, copy the code, create a new query in Power Query, open the advanced editor and replace everything in there with the copied code. Name this query “Xls_WORKDAY”.

This function’s parameters work just like the Excel function:

  • StartDate: A date field as your reference
  • Days: A number field that defines how many days forward or backwards you want to go. (negative values go backwards)
  • optional holidays: A list of optional holiday dates. If you want to use the UI that the function has created, your holidays must reside in a column of an existing table, that you can reference within the dialogue.

Enjoy and stay queryious 😉

Comments (7) Write a comment

  1. No field under caption “Holidays(Option)” how can I supply the holidays parameter to invoke the function? What’s the button “choose column” for?

    Reply

    • Hi Julian,
      if you want to use the function with the UI only, you need to have a table with the holiday days in a column that you can reference with “choose column”.
      If you don’t have such a table but want to pass in the holiday days manually in a list, you have to use the syntax as it is described in the function documentation section.

      Reply

  2. Sorry i’m a bit confused about the Holidays list. I have a separate list called Holidays, but i’m unclear as to how to pass in the holidays (not using the UI) but as a function.

    Reply

  3. Great code!! If wanted to choose Start Date and End Date which are located in columns instead of StartDate: A date field as your reference or EndDate: A date field as your reference.
    How do I accomplish this?

    Reply

  4. I am gettin:

    “An error occurred in the ‘’ query. Expression.Error: There weren’t enough elements in the enumeration to complete the operation.
    Details:
    [List]”

    only when = Xls_WORKDAY(#date(2022, 6, 25), 0, null)

    If using another date. It is ok. Any solution please?

    Reply

Leave a Reply

Die Datenschutzbestimmungen finden Sie hier: / Please find the privacy policy here: https://wp.me/P6lgsG-Rz