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

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's avatar
Anonymous
Not applicable
4 years ago

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.