Forum Discussion
Helper IIIcalculate date without holidays (only working days)
Helper IIIFirst of all thank you very much for coming so quickly to my aid AlienSx . really thank you
I was evaluating the code and I still have a problem, it basically boils down to the fact that it is adding a day to the departure date, therefore the result is not as expected, since I must count the days from the moment the departure date begins.
Prepare the following image where it will be clearer, for the month of March. I asked that 5 working days be calculated (yellow boxes), that is, starting on March 31, the function should return March 10, in this case it returns March 11.
In the case of June, I asked to return 15 business days, that is, my expected response would be July 14, but it returns July 17.
I tried to modify the code that you provided me but I was not able to obtain the solution, I leave it here modified and I hope that you can please guide me on how to solve this issue. Thank you very much in advance
// start = your date
// days = number of business days to add
// holidays = list with holidays (list of dates), can be empty list {}
(start as date, days as number, holidays as list) as date =>
let
next = Date.AddDays(start, 1),
holi = List.Contains(holidays, next) or List.Contains({5, 6}, Date.DayOfWeek(next,Day.Monday)),
result = if days = 0 then start
else @add_business_days(next, days - (if holi then 0 else 1), holidays)
in
result
Super UserI have a different algorithm to offer, but need you to answer two questions:
- What country are you dealing with so far as holidays are concerned?
- Are we to assume that Date of Departure + One Day = Date of Departure?
- jcamilo1985
first of all thank you very much ronrsnfld for coming to the solution of the thread
1 - holidays are for Colombia
2 - no, we start counting the days from the departure date.I annex the table of holidays that I have, the real table is older
holydays 1/01/2023 7/01/2023 8/01/2023 9/01/2023 14/01/2023 15/01/2023 21/01/2023 22/01/2023 28/01/2023 29/01/2023 4/02/2023 5/02/2023 11/02/2023 12/02/2023 18/02/2023 19/02/2023 25/02/2023 26/02/2023 4/03/2023 5/03/2023 11/03/2023 12/03/2023 18/03/2023 19/03/2023 20/03/2023 25/03/2023 26/03/2023 1/04/2023 2/04/2023 6/04/2023 7/04/2023 8/04/2023 9/04/2023 15/04/2023 16/04/2023 22/04/2023 23/04/2023 29/04/2023 30/04/2023 1/05/2023 6/05/2023 7/05/2023 13/05/2023 14/05/2023 20/05/2023 21/05/2023 22/05/2023 27/05/2023 28/05/2023 3/06/2023 4/06/2023 10/06/2023 11/06/2023 12/06/2023 17/06/2023 18/06/2023 19/06/2023 24/06/2023 25/06/2023 1/07/2023 2/07/2023 3/07/2023 8/07/2023 9/07/2023 15/07/2023 16/07/2023 20/07/2023 22/07/2023 23/07/2023 29/07/2023 30/07/2023 5/08/2023 6/08/2023 7/08/2023 12/08/2023 13/08/2023 19/08/2023 20/08/2023 21/08/2023 26/08/2023 27/08/2023 2/09/2023 3/09/2023 9/09/2023 10/09/2023 16/09/2023 17/09/2023 23/09/2023 24/09/2023 30/09/2023 1/10/2023 7/10/2023 8/10/2023 14/10/2023 15/10/2023 16/10/2023 21/10/2023 22/10/2023 28/10/2023 29/10/2023 4/11/2023 5/11/2023 6/11/2023 11/11/2023 12/11/2023 13/11/2023 18/11/2023 19/11/2023 25/11/2023 26/11/2023 2/12/2023 3/12/2023 8/12/2023 9/12/2023 10/12/2023 16/12/2023 17/12/2023 23/12/2023 24/12/2023 25/12/2023 30/12/2023 31/12/2023 - ronrsnfld
Thank you.
- Generate a calender including the possible date range from which holidays and weekend days are removed.
- To add working days, we merely look for the position in the List of working days that corresponds to then number of working days you wish to add.
- In your specific case, since the departure date is counted as Day 1, you must subtract 1 from the number of days you are looking for.
- Note that in the code below, I took your list of holidays and put in in a Query named HolyDay for access. But there is also code commented out that can download the holiday list for many countries from the web. (Using a Table for this will be much faster, however.
- I don't know if you will run into the same problem you had with the other solution
//Note optional Holidays parameter //You can substitute a self-generated List of holidays, or use the website in the code to download a list // If you use the website, be sure to use a recognized country name //If you are using holidays, you should separate this list and buffer the result for speed issues. // as written, it will download this list every time the function executes (start as datetime, numDays as number, optional Country as nullable text) => let adjNumDays = Number.RoundAwayFromZero(Number.Abs(numDays*7/5))*2, yrStart = Date.Year(start), yrEnd = Date.Year(Date.AddDays(start,adjNumDays*Number.Sign(numDays))), //Create list of country specific Holidays holidays = HolyDays[holydays], /*yrs = {List.Min({yrEnd,yrStart})..List.Max({yrEnd,yrStart})}, if Country = null then {} else List.Accumulate(yrs,{},(state,current) => let Source=Web.Page(Web.Contents("https://www.officeholidays.com/countries/" & Country & "/" & Number.ToText(current))), Data0 = Source{0}[Data], DateCol = Table.SelectColumns(Data0,"Date"), fullDate = List.Transform(DateCol[Date], each Date.FromText(_ & " " & Number.ToText(current))) in state & fullDate),*/ //all Working dates //Generates a list of working dates in either Ascending or Descending order // based on the Sign of numDays // starting with the first working date after Start allWorkDates = List.RemoveNulls( List.Transform( List.DateTimes(start, adjNumDays, #duration(Number.Sign(numDays),0,0,0)), each if _ = start then _ else if Date.DayOfWeek(_)=Day.Saturday or Date.DayOfWeek(_)=Day.Sunday or List.Contains(holidays, Date.From(_)) then null else _)), addWorkDays = if numDays = 0 then start else allWorkDates{Number.Abs(numDays)} in addWorkDays