Forum Discussion

DBATTIN's avatar
DBATTIN
Frequent Visitor
7 years ago
Solved

DATEDIFF Not Working in Custom Column

I am tyring to add the column as follows = Table.AddColumn(#"Added Custom", "OrderAge", each DATEDIFF ([DateTimeCreated],TODAY(),DAY))   But I get the meaasge    Expression.Error: The name 'DATE...
Need Help
  • PattemManohar's avatar
    PattemManohar
    7 years ago

    DBATTIN I guess you have DateTimeCreated field as DateTime datatype which also needs to converted into Date type. So please try this...

     

    Duration.Days(Duration.From(DateTime.Date(DateTime.LocalNow())-DateTime.Date([DateTimeCreated])))
DBATTIN's avatar
DBATTIN
Frequent Visitor
7 years ago

PattemManoharIt now will not recognise the funtion Today() is that for the same reason? If so what function do I need to use for Today please?

PattemManohar's avatar
PattemManohar
Community Champion
7 years ago

DBATTIN Please use DateTime.Date(DateTime.LocalNow())

  • DBATTIN's avatar
    DBATTIN
    Frequent Visitor
    7 years ago

    PattemManoharthe formula is now

     

    OrderAge = Duration.Days(Duration.From(DateTime.Date(DateTime.LocalNow())-[DateTimeCreated]))

     

    This is now showing no syntax errors but returns an error value in the custom column. Have I done something wrong?

     

     

    • PattemManohar's avatar
      PattemManohar
      Community Champion
      7 years ago

      DBATTIN I guess you have DateTimeCreated field as DateTime datatype which also needs to converted into Date type. So please try this...

       

      Duration.Days(Duration.From(DateTime.Date(DateTime.LocalNow())-DateTime.Date([DateTimeCreated])))
      • DBATTIN's avatar
        DBATTIN
        Frequent Visitor
        7 years ago

        PattemManoharthank you thats brilliant :smileyhappy: