Forum Discussion
Solved
Accurate Duration between datetimes
I have used the following query to generate a custom column of duration between two datetimes, excluding weekends and holidays but the result is rounding upto no of days and decimal places are displaying as 0. I need to show the duration in Exact decimal
Can anyone help me with this
(InitialDate as date, FinalDate as date, optional Holidays as list ) as number =>
let
HolidayDates = if Holidays=null then {null} else List.Select(Holidays, each _ is date ),
DaysBetweenDates = Duration.Days(FinalDate-InitialDate),
DaysList = List.Dates(List.Min({InitialDate,FinalDate}),Number.Abs(DaysBetweenDates)+1, Duration.From(1)),
WeekDaysList = List.Select(DaysList, each (Date.DayOfWeek(_, Day.Monday) < 5) ),
WeekDaysNoHolidaysList = List.RemoveItems(WeekDaysList, HolidayDates ),
WorkingDays = (if DaysBetweenDates < 0 then -1 else 1) * List.Count(WeekDaysNoHolidaysList)
in
WorkingDays
Can anyone help me with this
(InitialDate as date, FinalDate as date, optional Holidays as list ) as number =>
let
HolidayDates = if Holidays=null then {null} else List.Select(Holidays, each _ is date ),
DaysBetweenDates = Duration.Days(FinalDate-InitialDate),
DaysList = List.Dates(List.Min({InitialDate,FinalDate}),Number.Abs(DaysBetweenDates)+1, Duration.From(1)),
WeekDaysList = List.Select(DaysList, each (Date.DayOfWeek(_, Day.Monday) < 5) ),
WeekDaysNoHolidaysList = List.RemoveItems(WeekDaysList, HolidayDates ),
WorkingDays = (if DaysBetweenDates < 0 then -1 else 1) * List.Count(WeekDaysNoHolidaysList)
in
WorkingDays
Hi Sadab ,
We can try to use the following queries to meet your requirement:
(InitialDate as datetime, FinalDate as datetime, optional Holidays as list ) as number => let HolidayDates = if Holidays=null then {null} else List.Select(Holidays, each _ is date ), DaysBetweenDates = Duration.Days(DateTime.Date(FinalDate)-DateTime.Date(InitialDate)), DaysList = List.Dates(List.Min({DateTime.Date(InitialDate),DateTime.Date(FinalDate)}),Number.Abs(DaysBetweenDates)+1, Duration.From(1)), WeekDaysList = List.Select(DaysList, each (Date.DayOfWeek(_, Day.Monday) < 5) ), WeekDaysNoHolidaysList = List.RemoveItems(WeekDaysList, HolidayDates ), WorkingDays = (if DaysBetweenDates < 0 then -1 else 1) * List.Count(WeekDaysNoHolidaysList), ActualWorkingDays = WorkingDays - (if List.Contains(WeekDaysNoHolidaysList,DateTime.Date(InitialDate)) then Duration.Seconds(InitialDate - DateTime.From(DateTime.Date(InitialDate)))/(3600*24) else 0) - (if List.Contains(WeekDaysNoHolidaysList,DateTime.Date(FinalDate)) then Duration.Seconds(Date.AddDays(DateTime.From(DateTime.Date(FinalDate)),1) - FinalDate)/(3600*24) else 0) in ActualWorkingDays
Best regards,
1 Reply
Hi Sadab ,
We can try to use the following queries to meet your requirement:
(InitialDate as datetime, FinalDate as datetime, optional Holidays as list ) as number => let HolidayDates = if Holidays=null then {null} else List.Select(Holidays, each _ is date ), DaysBetweenDates = Duration.Days(DateTime.Date(FinalDate)-DateTime.Date(InitialDate)), DaysList = List.Dates(List.Min({DateTime.Date(InitialDate),DateTime.Date(FinalDate)}),Number.Abs(DaysBetweenDates)+1, Duration.From(1)), WeekDaysList = List.Select(DaysList, each (Date.DayOfWeek(_, Day.Monday) < 5) ), WeekDaysNoHolidaysList = List.RemoveItems(WeekDaysList, HolidayDates ), WorkingDays = (if DaysBetweenDates < 0 then -1 else 1) * List.Count(WeekDaysNoHolidaysList), ActualWorkingDays = WorkingDays - (if List.Contains(WeekDaysNoHolidaysList,DateTime.Date(InitialDate)) then Duration.Seconds(InitialDate - DateTime.From(DateTime.Date(InitialDate)))/(3600*24) else 0) - (if List.Contains(WeekDaysNoHolidaysList,DateTime.Date(FinalDate)) then Duration.Seconds(Date.AddDays(DateTime.From(DateTime.Date(FinalDate)),1) - FinalDate)/(3600*24) else 0) in ActualWorkingDays
Best regards,