Forum Discussion
how to dynamically calculate the difference between 2 dates without counting bank holidays and we ?
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 ...
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}}), ...
Hello,
Thank you but I was referring to do that in Power Query M language, do you know then please ?
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"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 ?
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}}), ...