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

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.