Accurate Duration between datetimes

[Sadab]

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

[v-lid-msft · Accepted solution]

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,