Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

dateDiff in weekdays function

Hi there,    i've been using the following function to calculate the date difference in weekdays between 2 columns (creationDate, closureDate);   = (InitialDate as date, FinalDate as date ) as nu...
  • ChrisClever's avatar
    ChrisClever
    4 years ago

    Ok,

    So the problem here is the null value in the FinalDate Column. The easy fix would be to replace the nulls with the current date before adding the custom function column. And then your code should be working just fine.

    #"Replace Value" = Table.ReplaceValue(#"Changed type",null,Date.From(DateTime.LocalNow()),Replacer.ReplaceValue,{"Final Date"})

     

     

ChrisClever's avatar
ChrisClever
Frequent Visitor
4 years ago

This is a PQ question Anonymous ðŸ™‚ But take a look at this:

 

 

= (InitialDate as date, FinalDate as date) as number =>
let
FinalDateCheck = Number.From(each if [Final Date] = null then Date.From(DateTime.LocalNow()) else [Final Date]),
DaysBetweenDates = Duration.Days(FinalDateCheck-InitialDate),
DaysList = List.Dates(List.Min({InitialDate,FinalDateCheck}),Number.Abs(DaysBetweenDates)+1, Duration.From(1)),
WeekDaysList = List.Select(DaysList, each (Date.DayOfWeek(_, Day.Monday) < 5) ),
WorkingDays = (if DaysBetweenDates < 0 then -1 else 1) * List.Count(WeekDaysList)
in
WorkingDays

 

I modified your function with a simple if statement.