Transform Date format without separation

[IMRGZ]

Hi,

I have a question regarding the options to transform my specific date format into a 'normal' date format.

Dates are importes as numbers without separators like this (Dutch format):

18012019

9012019

However I would like these dates as follows:

18-01-2019

09-01-2019

I have checked the forum and other pages but I cannot find a solution. I have checked the options available under change type and then the options for "date" or "Using locale", but I can't find the right type of setting.

Any suggestions?

Thank you for your kind help.

[jmalone · Accepted solution]

The day can be single digits ("9" in your example), but the months are always two digits ("01")?

To handle this, I've added a conditional statement that accommodates it. Otherwise if the days are always two digits, you can just use the first section (where Text.Length = 8).

This assumes your imported date field is a Text format (I used [SourceDate] as the field name...just replace it with the real column name).

Edit: I didn't mention at first, but you need to Add Column > Custom Column, then insert the following code:

if Text.Length([SourceDate]) = 8 
then
Text.Start([SourceDate], 2) 
&"-" & 
Text.Start( Text.End([SourceDate], 6) , 2)
& "-" & 
Text.End([SourceDate], 4)
else 
Text.Start([SourceDate], 1) 
& "-" & 
Text.Start( Text.End([SourceDate], 6) , 2)
& "-" & 
Text.End([SourceDate], 4)

[ChrisMendoza · Accepted solution]

@IMRGZ -

Personally I would convert to Date/Time Data Type and use the built-in formatting options.

DAX Calculated Column:

ConvertedToDate = 
VAR isLEN8 =
    IF ( LEN ( Table1[unformattedDate] ) = 8, TRUE (), FALSE () )
RETURN
    IF (
        isLEN8,
        DATE ( RIGHT ( Table1[unformattedDate], 4 ), MID ( Table1[unformattedDate], 3, 2 ), LEFT ( Table1[unformattedDate], 2 ) ),
        DATE ( RIGHT ( Table1[unformattedDate], 4 ), MID ( Table1[unformattedDate], 2, 2 ), LEFT ( Table1[unformattedDate], 1 ) )
    )

US formatting option allows me to format as yyyy-MM-dd.

https://docs.microsoft.com/en-us/dax/date-function-dax

---

---

[jmalone · Accepted solution]

The day can be single digits ("9" in your example), but the months are always two digits ("01")?

To handle this, I've added a conditional statement that accommodates it. Otherwise if the days are always two digits, you can just use the first section (where Text.Length = 8).

This assumes your imported date field is a Text format (I used [SourceDate] as the field name...just replace it with the real column name).

Edit: I didn't mention at first, but you need to Add Column > Custom Column, then insert the following code:

if Text.Length([SourceDate]) = 8 
then
Text.Start([SourceDate], 2) 
&"-" & 
Text.Start( Text.End([SourceDate], 6) , 2)
& "-" & 
Text.End([SourceDate], 4)
else 
Text.Start([SourceDate], 1) 
& "-" & 
Text.Start( Text.End([SourceDate], 6) , 2)
& "-" & 
Text.End([SourceDate], 4)

[IMRGZ]

@jmalone @ChrisMendoza 

Thank you for your help. Both solutions actually worked out nicely.

Indeed, I had 8 and 9 length digits.

I just had to copy in your codes with the right variables and it worked.

I am still really amazed about the quickness of response! Thanks again.