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 (14) 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

  5. I get an error for all non zero values. I am not sure what I am doing wrong.

    An error occurred in the ‘Xls_WORKDAY’ query. Expression.Error: The number is out of range of a 32 bit integer value.
    Details:
    53.9375

    Reply

  6. Hi Imke,

    Thanks for writing this function. There seems to be a bug in your function. In most occassions your functions works properly except if one tries to add workdays to a date that falls in the weekends, because the weekend days are removed.

    Example:
    = fx_add_workdays(#date(2022, 8, 12), 10) returns 26/08/2022
    = fx_add_workdays(#date(2022, 8, 13), 10) returns 29/08/2022
    = fx_add_workdays(#date(2022, 8, 14), 10) returns 29/08/2022

    Some if else function is required to add workdays to a date that falls in the weekend as if you add days to the friday before that weekend.

    Kind regards,

    Arjan

    Reply

    • Solved:

      add below debug parameters:

      StartDateLocal =
      if Date.DayOfWeek(StartDate, Day.Monday) = 6 then
      Date.AddDays(StartDate, -2)
      else if Date.DayOfWeek(StartDate, Day.Monday) = 5 then
      Date.AddDays(StartDate, -1)
      else StartDate,

      Replace all StartDate below this code with StartDateLocal

      Reply

  7. Pingback: Calculate Nth Business Day From Date in Power Query - BI Gorilla

  8. Thank you for the great code ! In my exercise the holidays changes country by country . In main query ARRIVALS i have records of arrival in different country . I want to calculate the leadtime considering the holidays in the related country for each record in ARRIVAL. Therefore for 3rd parameter of Xls_WORKDAY, holidays, i need to use a SelectRows and Table.Column function that filters the country in the table HOLIDAYS ,

    I have wrote something like this

    Xls_WORKDAY( Startdate,LTdays,
    each let
    wh = [COUNTRY]
    in
    Table.Column(Table.SelectRows(HOLIDAYS,each [CO]= wh),”Date”))

    where COUNTRY is the country in ARRIVAL query and CO is the country in HOLIDAYS table.
    “Date” is the column for holiday date in table HOLIDAYS

    When I run it, I got below error ..

    The function Table .Column returns a list that is the type required for holidays parameter in XLs_WORKDAY .
    I cannot understand ..

    Can somebody help me , please ?

    Expression.Error: We cannot convert a value of type Function to type List.
    Details:
    Value=[Function]
    Type=[Type]

    Reply

  9. Thanks Imke, Your code is working fine, I spent 22 working hours to implement different solutions but nothing was perfect, glad that I found your code and it is working fine. Thanks again.

    Reply

Leave a Reply