dateDiff in weekdays function

[Anonymous]

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 number =>
let
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) ),
WorkingDays = (if DaysBetweenDates < 0 then -1 else 1) * List.Count(WeekDaysList)
in
WorkingDays

The function lets me select 2 columns, but since i'm trying to calculate processing time and i'm only using creation- and closureDate, i can only calculate the processing time of tickets already closed. I'm trying to get a third column into the mix that takes the currentDate if closureDate is empty, but i can't seem to get that IF statement to work at all 😕  anyone knows the solution?

Thanks in advance,

[Anonymous]

Hi there @ChrisClever , 

I just noticed that i had selected the wrong forum; oops :0

I've tried the adjustment to the function, but it gives the following error:

DataFormat.Error: can't convert to Number.
Details:
[Function]

I'm guessing there is something wrong with the data types somewhere, so i changed the intialdate and finaldate to datetime in the first line, but it hasn't fixed the error.

i'm not quite sure what exaclty can't be converted to number 😕

[ChrisClever · Accepted solution]

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"})

[Anonymous]

Thanks so much!

The original function works fine now that all the null values out of closureDate have been replaced by DateTime.LocalNow

Thanks again ^^

[ChrisClever]

Did you copy and paste the entire code?

My guess is you've just added the FinalDateCheck line without modifying the rest of the code 🙂 

Take a close look - you need to change FinalDate to FinalDateCheck in your calculations.

[Anonymous]

yup, i've copied all of the code, and tried all of the data types. 

The function should give dateDiff in weekdays as whole numbers.

Still unclear what causes the conversion to number error.

I tried changing line 3;

FinalDateCheck = Number.From(each.....

to:

FinalDateCheck = Date.from(each....

and now it gives the same DataFormat.Error except for number it now says date:

can't convert to date

lastly i've removed the spaces between Final Date in line 3, sadly without effect.

[ChrisClever]

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.