Forum Discussion

jcamilo1985's avatar
jcamilo1985
Icon for Helper III rankHelper III
3 years ago
Solved

calculate 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 co...
Need Help
  • AlienSx's avatar
    AlienSx
    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.  

  • jcamilo1985's avatar
    jcamilo1985
    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

     

     

     

jcamilo1985's avatar
jcamilo1985
Icon for Helper III rankHelper III
3 years ago

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

 

AlienSx's avatar
AlienSx
Icon for Super User rankSuper User
3 years ago

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_date

 This test (June 23rd with July 3rd as holiday and 15 days) gives July 14th.

  • jcamilo1985's avatar
    jcamilo1985
    Icon for Helper III rankHelper III
    3 years ago

    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.

    • AlienSx's avatar
      AlienSx
      Icon for Super User rankSuper User
      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.  

      • jcamilo1985's avatar
        jcamilo1985
        Icon for Helper III rankHelper III
        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