how to dynamically calculate the difference between 2 dates without counting bank holidays and we ?

[jmclej2]

Hello,

This url gives me the french bank holidays : https://calendrier.api.gouv.fr/jours-feries/metropole.json

I have a table with a datetime column called CreationDateTime. Can you please tell me how I can add a personnalized column that would calculate the duration between now and CreationDateTime, not counting the saturdays nor the sundays nor the bank holidays there could be in between ?

[CNENFRNL]

[jmclej2]

Hello,

Thank you but I was referring to do that in Power Query M language, do you know then please ?

[CNENFRNL]

let
    Source = Json.Document(Web.Contents("https://calendrier.api.gouv.fr/jours-feries/metropole.json")),
    Ferie = List.Transform(Record.FieldNames(Source), Date.From),

    Date_Start = #date(2017,2,7),
    Date_End = #date(2020,5,19),
    #"Excluded Ferie" = List.Difference(List.Dates(Date_Start, Duration.Days(Date_End - Date_Start) + 1, #duration(1,0,0,0)), Ferie),
    #"Excluded Weekend" = List.Count(List.Select(#"Excluded Ferie", each Date.DayOfWeek(_, Day.Monday)<5))
in
    #"Excluded Weekend"

[jmclej2]

Hello, it's kind of what I want, but the need is to have the duration in a new column of a table, being the time elapsed between the value in column CreationDateTime (which is a DateTime and not a Date) and now (including the time part). Do you know how to do that ?

[jmclej2 · Accepted solution]

I have made up this solution which seems to work so far :

...
fnDuration = (startDate as datetime, endDate as datetime) =>
        let
            DurationDays = Duration.Days(endDate - startDate),
            ListDates = List.Dates(Date.From(startDate), DurationDays + 1, #duration(1, 0, 0, 0)),
            ListWeekDays = List.Select(ListDates, each Date.DayOfWeek(_, Day.Monday) < 5),
            ListHolidays = List.Transform(ListWeekDays, each Date.ToText(_, "yyyy-MM-dd")),
            Url = "https://calendrier.api.gouv.fr/jours-feries/metropole/" & Text.From(Date.Year(startDate)) & ".json",
            Holidays = Json.Document(Web.Contents(Url)),
            #"Converti en table" = Record.ToTable(Holidays),
            #"Colonnes supprimées" = Table.RemoveColumns(#"Converti en table",{"Value"}),
            HolidaysTrimmed = Table.ToList(#"Colonnes supprimées"),
            ListInterSect = List.Intersect({ListHolidays, HolidaysTrimmed}),
            Result = endDate - startDate - #duration(List.Count(ListInterSect) + DurationDays + 1 - List.Count(ListWeekDays), 0, 0, 0)
    in Result,
    AddedCustom = Table.AddColumn(#"Type modifié", "DiffDate", each fnDuration([sys_created_on], DateTime.FromText(DateTime.ToText(DateTime.LocalNow(), [Format="dd-MMM-yyyy HH:mm:ss", Culture="en-US"])))),
    #"Type modifié3" = Table.TransformColumnTypes(AddedCustom,{{"DiffDate", type duration}}),
...