Forum Discussion

Brett007's avatar
Brett007
Icon for Helper III rankHelper III
6 years ago
Solved

Duration between two dates

I would like to calculate the difference between Today's Date and the Date in the [Assigned Date] Column.

I wanted to present the answer in the form of days. 

 

I used the following formula, but the whole column shows errors.

 

Duration.Days((DateTime.FixedLocalNow())-([Assigned Date]))

 

Any Suggestions

 

Need Help
  • dax's avatar
    dax
    6 years ago

    Hi Brett007 , 

    You could try below M code to see whether it work or not

    Duration.Days(Duration.From(DateTime.Date(DateTime.LocalNow())-[Assigned Date]))

     

    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

7 Replies

  • dax's avatar
    dax
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Brett007 , 

    You could try below M code to see whether it work or not

    Duration.Days(Duration.From(DateTime.Date(DateTime.LocalNow())-[Assigned Date]))

     

    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Probably needs to be something like:

     

    Number.From([Date2] - [Date1]) + 1

     

    ?

    • Brett007's avatar
      Brett007
      Icon for Helper III rankHelper III
      6 years ago

      I tried, but I did not get a change in the results. 

       
       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi Brett007 ,

         

        I think the problem is not with the formula - it is in the data type. PBI generally does not support "silent" conversions.

        From what I can see the [Assigned Date]  is type date and the DateTime.FixedLocalNow() returns type datetime. These types are not directly compatible and, most likely, you have an error like this:

        Expression.Error: We cannot apply operator - to types DateTime and Date.
Details:
    Operator=-
    Left=10/03/2020 8:51:05 AM
    Right=2/01/2020

        Either convert the [Assigned Date]  to type datetime or adjust your formula to force datetime to date type converstion:

        Date.From(DateTime.FixedLocalNow())

        Kind regards,

        JB

         

  • gpoggi's avatar
    gpoggi
    Icon for Responsive Resident rankResponsive Resident
    6 years ago

    Hi there, assuming your [Assigned Date] is in DateTime format, you only have to add Duration.From to your code, so try something like:

     

    Duration.Days(Duration.From(DateTime.FixedLocalNow()-[Assigned Date]))

     

    And you should get what you need, please let me know if it works.

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    Regards,

     

    Gian Carlo Poggi

  • monsheet's avatar
    monsheet
    New Member
    5 years ago

    But then, how so I know which data type both are. They appear to be the same.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi monsheet ,

       

      datetime would have hh:mm:ss part, date  doesn't have and doesn't show it. Dates in the column on the screenshot in this case do not have hours and minutes, mean these are in date format.

       

      Kind regards,

      jb