Forum Discussion
Helper IIIcalculate date without holidays (only working days)
Good night
After trying for several hours I had no choice but to resort to your help
I have the following challenge, I have a table with a date to which I have to add a number of days based on a condition in another column, something like if column A is equal to x value then add 2 days to the date column otherwise add 15 days. It looks as follows.
The problem comes from the fact that the days that must be added are not calendar days, but rather they must not consider Saturdays, Sundays or holidays.
In this aspect I have a table with a single column that has the dates of all Saturdays, Sundays and holidays that must be removed.
In summary I must take date_income and if request_type is x value then add 15 business days otherwise add 2 business days
I don't know how to perform a function that allows me to calculate a similar function. If someone can help me, I would greatly appreciate it.
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.
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
12 Replies
- AlienSx
Super User
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)
jcamilo1985First 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- AlienSx
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.
Hello,
I think this should work. Again, as you mentioned, one main table and one table with the holiday and weekend dates, like so:
Name of the weekend and holiday dates query should be "WeekendHolidays" and column name should be as above "HolidayWeekend" or you have to adjust the m code based on your names
M code:
let Source = Excel.CurrentWorkbook(){[Name="Dates"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date_income", Int64.Type}, {"RequesTType", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let Plus15days = List.RemoveMatchingItems({Number.From([Date_income])..[Date_income]+15}, WeekendHolidays[HolidayWeekend]), Plus2Days = List.RemoveMatchingItems({Number.From([Date_income])..[Date_income]+2}, WeekendHolidays[HolidayWeekend]) in if Text.Upper([RequesTType]) = "X" then Plus15days else Plus2Days), #"Added Custom1" = Table.AddColumn(#"Added Custom", "NewDate", each [Date_income] + (List.Count([Custom])-1)), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"NewDate", type date}, {"Date_income", type date}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Custom"}) in #"Removed Columns"This is the output based on the m code:
