Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PStokes
Frequent Visitor

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

 
1 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)))

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

@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)))

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

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)))

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors