Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Dateformat

Hello everyone, 

 

Can anyone help me to convert this text column into a date column? 

The text value looks like this 15-FEB-20 11.00.00.000000 PM.

 

Date
Date and Time
date format
  • az38's avatar
    az38
    6 years ago

    Hi Anonymous 

    if you need exactly DAX, not M formula use:

    a. for only date

    ColumnDate = DATEVALUE(FORMAT(LEFT([Columntext],9),"General Date"))

    b. for the datetime column

    ColumnDateTime = 
var _date = DATEVALUE(FORMAT(LEFT([Columntext],9),"General Date"))
var _time = TIMEVALUE(SUBSTITUTE(RIGHT([Columntext],LEN([Columntext])-10),".000000", ""))

RETURN
_date + _time

6 Replies

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Hi Anonymous 

    if you need exactly DAX, not M formula use:

    a. for only date

    ColumnDate = DATEVALUE(FORMAT(LEFT([Columntext],9),"General Date"))

    b. for the datetime column

    ColumnDateTime = 
var _date = DATEVALUE(FORMAT(LEFT([Columntext],9),"General Date"))
var _time = TIMEVALUE(SUBSTITUTE(RIGHT([Columntext],LEN([Columntext])-10),".000000", ""))

RETURN
_date + _time
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      When i try to run the formula i get this error: 
      "Cannot convert value '' of type Text to type Date."

      Do you have any solution for this? 

      • az38's avatar
        az38
        Community Champion
        6 years ago

        Anonymous 

        do you have empty cells with data? if so, how do you want to calculate it? what date it should return?