-
-
Save ImkeF/ecb5c0fd6ef791ae4beffd11e6d3f788 to your computer and use it in GitHub Desktop.
Returns the number of whole working days between StartDate and EndDate similar to the NETWORKDAYS-function in Excel. Working days exclude weekends and any dates identified in holidays.
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
// fnNETWORKDAYS | |
let func = | |
(StartDate, EndDate, optional Holidays as list, optional StartOfWeek as number) => | |
let | |
// optional StartOfWeek, if empty the week will start on Monaday | |
startOfWeek = if StartOfWeek = null then 1 else StartOfWeek, | |
// cater for empty dates | |
StartEmpty = if StartDate = null then #date(1900,1,1) else StartDate, | |
EndEmpty = if EndDate = null then #date(1900,1,1) else EndDate, | |
// Providing for logic where EndDate is after StartDate | |
Start = List.Min({StartEmpty, EndEmpty}), | |
End = List.Max({StartEmpty, EndEmpty}), | |
// Switch sign if EndDate is before StartDate | |
Sign = if EndEmpty < StartEmpty then -1 else 1, | |
// Get list of dates between Start- and EndDate | |
ListOfDates = List.Dates(Start, Number.From(End - Start) + 1,#duration(1,0,0,0)), | |
// 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, List.Transform(Holidays, Date.From )), | |
// 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(_, startOfWeek) < 5 ), | |
// Count the number of days (items in the list) | |
CountDays = List.Count(DeleteWeekends) * Sign | |
in | |
CountDays , | |
documentation = [ | |
Documentation.Name = " Date.Networkdays.pq ", | |
Documentation.Description = " Returns the number of whole working days between StartDate and EndDate similar to the NETWORKDAYS-function in Excel. Working days exclude weekends and any dates identified in holidays. ", | |
Documentation.LongDescription = " Returns the number of whole working days between StartDate and EndDate similar to the NETWORKDAYS-function in Excel. Working days exclude weekends and any dates identified in (optional) holidays. ", | |
Documentation.Category = " Date ", | |
Documentation.Source = " www.TheBIccountant.com https://wp.me/p6lgsG-2fA . ", | |
Documentation.Version = " 3.1 Catering for empty dates", | |
Documentation.Author = " Imke Feldmann ", | |
Documentation.Examples = {[Description = " ", | |
Code = " ", | |
Result = " "]}] | |
in | |
Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation)) |
Hardened function (automatic conversion to date)
Adjusted so that negative durations where EndDate is before StartDate can be returned.
Hello... First of all thank you, this has been very helpful.
I would like to ask what needs to be done with the code for it to substract the EndDate and the StartDate? I have Dates with time stamp as well and need a way for the code to take in count the hours as well.
Appreciate all the help you are able to provide around this!
Hello @iBlfxAL ,
I would start with taking this function as it is. As it will return more a longer duration than you need you'd have to calculate the hours to subtract:
- Duration.From( DateTime.Time([StartDate]) - #time(0,0,0)) will give the duration until the StartDateTime and
- Duration.From( #time(24,0,0) - DateTime.Time([EndDate])) will give the remaining hours until the end of the day
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Fixed error in step "ListOfDates"