convert 7 character text DMMYYY and 8 character text DDMMYYYY column to date

[Anonymous]

@PStokes Please try below formula. in case it doesn't work for you please share two three record for each formate

DDMMYYYY = DATE(RIGHT('Table'[Date],4),MID('Table'[Date],3,2),LEFT('Table'[Date],2))

In case of DMMYYYY please check the length of column and if it is 8 then you can use above else

dMMYYYY = 
VAR _length = len('table'[column])
RETURN IF(_length=7,DATE(RIGHT('Table'[Date],4),MID('Table'[Date],3,2),LEFT('Table'[Date],1)),DATE(RIGHT('Table'[Date],4),MID('Table'[Date],3,2),LEFT('Table'[Date],2)))

[PStokes]

Thanks, I tried to add colum and use the VAR as written but received a sytax error

Examples of the data below. 

Date Text

1012019

21012019

27112019

8112019

[PStokes · Accepted solution]

sorry saw my syntax error and corrected it.

This worked if I chnaged the MID expression to  MID('table'[date],2,2) for 7 character dates and MID('table'[date],3,2) for 8 character dates

successful formulae below

thank you very much

Date =

VAR _length = LEN('table'[date])

RETURN IF(_length=7,DATE(RIGHT('table'[date],4),MID('table'[date],2,2),LEFT('table'[date],1)),DATE(Right('table'[date],4),MID('table'[date],3,2),LEFT('table'[date],2)))