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

     

     

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
ChrisClever
Frequent Visitor
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"})

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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 ^^