Change whole number to date

[Anonymous]

I have excel files that always display the date as 5312021. I want to change this to an actual date format. The other articles I've read have not worked for me. Any help is appreciated. I'm still learning how this all works. Example below.

[ryan_mayu · Accepted solution]

@Anonymous 

pls try this

Column = if(len('Table (4)'[date])=7,date(right('Table (4)'[date],4),left('Table (4)'[date],1),mid('Table (4)'[date],2,2)),date(right('Table (4)'[date],4),left('Table (4)'[date],2),mid('Table (4)'[date],3,2)))

[FrankAT · Accepted solution]

Hi @Anonymous ,

use the following formula for a calculated column:

Date = 
DATE(
    RIGHT('Table'[ORDER_DATE],4), 
    LEFT(RIGHT('Table'[ORDER_DATE],6),2), 
    LEFT('Table'[ORDER_DATE], LEN('Table'[ORDER_DATE]) - 6)
)

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[mahoneypat]

What happens when the day is <10?  Do you see 522021 or 5022021, for example?  And if the month is >9?  Do you see 1122021 or 11022021?

Pat

[Anonymous]

It displays 2 digits for the day on all dates 5022021. Will display 1 or 2 digit month.

[ryan_mayu · Accepted solution]

@Anonymous 

pls try this

Column = if(len('Table (4)'[date])=7,date(right('Table (4)'[date],4),left('Table (4)'[date],1),mid('Table (4)'[date],2,2)),date(right('Table (4)'[date],4),left('Table (4)'[date],2),mid('Table (4)'[date],3,2)))

[Anonymous]

I tried this and I got the following error.

[ryan_mayu]

@Anonymous 

not create a column in PQ, use DAX to create a column