Forum Discussion
calculate date without holidays (only working days)
- 3 years ago
jcamilo1985 I am afraid I can't comment on this. Probably because my function uses recursive call. I don't know. It's quite easy to reproduce this function using List.Generate. I'll try to do that a bit later, mate.
- 3 years ago
Thank you very much for your commitment and interest in this topic AlienSx .
After several attempts, I was finally able to find the solution, but all thanks to the base that you gave me.Here I share it in case someone else needs it at some point.
In the same way thanks to the other people who read the thread and were interested.(start as date, days as number, holidays as list) as date => let dates = List.Generate( () => [ date = start, count = 1, holiday = List.Contains(holidays, start) or List.Contains({5, 6}, Date.DayOfWeek(start, Day.Monday)) ], each [count] <= days, each [ date = Date.AddDays([date], 1), count = [count] + (if [holiday] then 0 else 1), holiday = List.Contains(holidays, Date.AddDays([date], 1)) or List.Contains({5, 6}, Date.DayOfWeek(Date.AddDays([date], 1), Day.Monday)) ], each [date] ), result = List.LastN(dates, 1){0} in result
Hi, jcamilo1985 try this function
// start = your date
// days = number of business days to add
// holidays = list with holidays (list of dates), can be empty list {}
add_business_days = (start as date, days as number, holidays as nullable list) as date =>
let
next = Date.AddDays(start, 1),
holi = List.Contains(holidays, next) or List.Contains({6, 0}, Date.DayOfWeek(next))
in
if days = 0 then start
else @add_business_days(next, days - (if holi then 0 else 1), holidays)- jcamilo19853 years ago
Helper III
First 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- AlienSx3 years ago
Super User
jcamilo1985 so basically when you "add" 1 business day to March 31st you get the same date? Okay. Just change "exit" condition to result = if days = 1 then start like in the code below.
let // start = your date // days = number of business days to add // holidays = list with holidays (list of dates), can be empty list {} add_business_days = (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 = 1 then start else @add_business_days(next, days - (if holi then 0 else 1), holidays) in result, s = #date(2023, 06, 23), d = 15, h = {#date(2023, 04, 06), #date(2023, 04, 07), #date(2023, 07, 03)}, next_b_date = add_business_days(s, d, h) in next_b_dateThis test (June 23rd with July 3rd as holiday and 15 days) gives July 14th.
- jcamilo19853 years ago
Helper III
Simply brilliant, the function is returning the expected result, however now I have another unexpected problem.
I am running the code in power query online for a datamart
and I'm getting this error at the time of data load:
AlienSx Are you familiar with this message, what can I do? Thanks again for the feature.
- ronrsnfld3 years ago
Super User
I 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?
- jcamilo19853 years ago
Helper III
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