Turn date mm-yy into mm-yyyy

[alexcollin24]

Hi all,

Sorry if this question might be simple to answer, but I just really don't know how to do it. I have a column Time where the date format is like this : mm-yy. In order to make a relationship with my Dimension Date, I have to transform my dates into mm/dd/yyyy. My column is in Text, but as soon as I click on the button to make it date format, all the dates below Jan-17 mark error. I don't really care about which day it will show. The important thing is that the month and the year are the same as they were in the column Time.

Can you please tell me how to transform mm-yy into mm/dd/yyyy?

Thank you,

alexcollin24

[TheOckieMofo]

YOu should be able to create a calculated column that will create a dummy date with this syntax:

Date("20"&RIGHT([Date_Column],2),LEFT([Date_Column,2),1)

This will add a 20 zero to all years (assuming you aren't working with data before the year 2000), extract the month from the text, and just set all the dates to the first of the month since you stated it didn't matter the exact day.

Hope this helps.

[alexcollin24]

Good, and if I have dates before 2000, do I create another calulated column?

[TheOckieMofo · Accepted solution]

No, you could handle that in the same column. You'd have to do an if statement to catch everything that's in the 1900's. So say you have data going back to 1950. You could do:

=IF(RIGHT([DATE_COLUMN],2)>=50,DATE("19"&RIGHT([DATE_COLUMN],2),LEFT([DATE_COLUMN],2),1),....

You'd place the rest of the syntax where I put the "...." I just got lazy.

No if you have data that overlaps, say you have data for 1915 and 2015, then you're probably going to have to go back to your source data and change the format of the source data.

[alexcollin24]

Last question, if some of my month are in text, for instance Jan-08 instead of 01-08, what should I do?