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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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)) |
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 😉
No field under caption “Holidays(Option)” how can I supply the holidays parameter to invoke the function? What’s the button “choose column” for?
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.
Thanks for your guidance.I’ll follow accordingly.
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.
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?
Great article, thank you!
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?
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
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
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
Pingback: Calculate Nth Business Day From Date in Power Query - BI Gorilla
Great post Imke. I’ve learned a lot from how you document your functions.
Your approach uses List.Functions to arrive at the nth working day. I today posted an article to tries to achieve the same but with a List.Generate approach.
This one iterates through each day until it reaches the desired working day. If you don’t mind I’ll share it here for further reading:
https://gorilla.bi/power-query/nth-business-day-from-date/
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]
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.