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!