Forum Discussion

cgarcia's avatar
cgarcia
Frequent Visitor
9 years ago
Solved

Date Formatting MMDDYY from text

I'm running into a wall when trying to format text to dates. My column information looks like this. 080613 011416 000000 111214   I used this formula Column = LEFT(FILE[COLUMN 1],2)&"/"&MID(FI...
Need Help
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    9 years ago

    Hi cgarcia,

     

    As the value "00/00/2000" is not a valid date, when you change the data type to date, the error throws out. You can modify the DAX to get around of this kind of invalid date value:

     

    Column = IF( LEFT(FILE[COLUMN 1],2)="00" && MID(FILE[COLUMN 1],3,2)="00",BLANK(),LEFT(FILE[COLUMN 1],2)&"/"&MID(FILE[COLUMN 1],3,2)&"/"&RIGHT(FILE[COLUMN 1],2)+2000)

     

     

     

    Best Regards,
    Qiuyun Yu

v-qiuyu-msft's avatar
v-qiuyu-msft
Community Support
9 years ago

Hi cgarcia,

 

As the value "00/00/2000" is not a valid date, when you change the data type to date, the error throws out. You can modify the DAX to get around of this kind of invalid date value:

 

Column = IF( LEFT(FILE[COLUMN 1],2)="00" && MID(FILE[COLUMN 1],3,2)="00",BLANK(),LEFT(FILE[COLUMN 1],2)&"/"&MID(FILE[COLUMN 1],3,2)&"/"&RIGHT(FILE[COLUMN 1],2)+2000)

 

 

 

Best Regards,
Qiuyun Yu

cgarcia's avatar
cgarcia
Frequent Visitor
9 years ago

Hi Qiuyun,

 

This expression worked perfectly! What if i wanted the value to be today's date instead of a blank?

 

Regards,

Chris