Converting Text to Year format "YYYY"

[pvural]

Hello everyone

I tried a few solutions here to convert text to date with no avail.  Here is my problem:

My report in excel comes with an "accounting period" column as text, example 201701. When I seperate columns 2017 and 01, and apply "sameperiodlastyear" using the year column, it doesn't recognize it as date, so formula comes back with error. When I changed the column to date 2017 becomes a weird mm/dd/yyyy format, something like November 15, 2050.

Basically what I am trying to do is to build the sameperiodlastyear for YOY calculations, but couldn't pass this little step to properly identify the "year" in the columns.

Thank you,

Petek

[Sean · Accepted solution]

@pvural

You need a Date Column

Use this formula in the Add Custom Column step shown below to transform your Period Column to a Date Column

 = Date.FromText ( [Period.2] & "/01/" & [Period.1] )

After you split the column convert to text

Add the Custom Column - Convert it to Date and remove old column if you wish

You can then use this Date column

[Sean · Accepted solution]

@pvural

You need a Date Column

Use this formula in the Add Custom Column step shown below to transform your Period Column to a Date Column

 = Date.FromText ( [Period.2] & "/01/" & [Period.1] )

After you split the column convert to text

Add the Custom Column - Convert it to Date and remove old column if you wish

You can then use this Date column

[pvural]

Thanks Sean.

The challenge is after splitting the columns, to only convert 2017 (Period 1) to a date format, not to bring month and year back to create a mm/dd/yyyy format... I just want to make sure Period 1 after the split will be recognized as a year as oppsed to a number or text so that i can apply sameperiodlastyear.

Having said that I can still use this solution, and will flag it as one. Thanks again!

Cheers,

PVural

[Sean · Accepted solution]

Once you have the Date Column you can get the Year and Month like this

[pvural]

Thank you ... Lots to learn 🙂

[Sean]

@pvural

BTW I just noticed you need to reverse my formula above to be like this (I'll change it too)

Period.2 first which is the Month then 1 and then the year - then you'll get the different months

= Date.FromText ( [Period.2] & "/01/" & [Period.1] )

 

Hope this helps!