Forum Discussion

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

     

     

     

Papermain's avatar
Papermain
Frequent Visitor
3 years ago

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: