Reply
Frequent Visitor
Posts: 4
Registered: ‎03-17-2017
Accepted Solution

Converting Text to Year format "YYYY"

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

 

 


Accepted Solutions
Super Contributor
Posts: 1,823
Registered: ‎08-11-2015

Re: Converting Text to Year format "YYYY"

[ Edited ]

@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 Smiley Happy

Query Editor - YYYYMM to Date.gif

 

View solution in original post

Super Contributor
Posts: 1,823
Registered: ‎08-11-2015

Re: Converting Text to Year format "YYYY"

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

 

Query Editor - YYYYMM to Date2.gif

View solution in original post


All Replies
Super Contributor
Posts: 1,823
Registered: ‎08-11-2015

Re: Converting Text to Year format "YYYY"

[ Edited ]

@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 Smiley Happy

Query Editor - YYYYMM to Date.gif

 

Frequent Visitor
Posts: 4
Registered: ‎03-17-2017

Re: Converting Text to Year format "YYYY"

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

Super Contributor
Posts: 1,823
Registered: ‎08-11-2015

Re: Converting Text to Year format "YYYY"

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

 

Query Editor - YYYYMM to Date2.gif

Frequent Visitor
Posts: 4
Registered: ‎03-17-2017

Re: Converting Text to Year format "YYYY"

[ Edited ]

Thank you ... Lots to learn Smiley Happy

Highlighted
Super Contributor
Posts: 1,823
Registered: ‎08-11-2015

Re: Converting Text to Year format "YYYY"

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

 Query Editor - YYYYMM to Date3.png

Hope this helps! Smiley Happy