Converting Values to a date

[Lucas in London]

Hi,

I have a spreadsheet I download that has dates stored in a non date format and I'm struggling to find an easy way to convert it to a format I can use.

The data is monthly and the dates are stored in the format: YYYY.MM - as shown for a sample of dates below:

2008.01
2008.02
2008.03
2008.04
2008.05
2008.06
2008.07
2008.08
2008.09
2008.1
2008.11
2008.12

I would like to conver these into the format: MMM-YY so for instance 2008.12 becomes Dec-2008 etc. I would usually use something like the text function (Text (value, "MMM-YY")) but this does not work has these entries are not recognised as dates! Anyone have any alternative ideas? The dates are in column A.

Thanks,

Lucas

 

[xenou]

I think with a formula you can do it like this:

If your value is in A1, this Formula is in B1:

=DATE(LEFT(A1,4),SUBSTITUTE(RIGHT(A1,2),".","")+1,0)

The idea is to strip out the period and then convert to a date with the year and month. But we assume we want the last day of the month -- as your dates don't have a day. is that okay? You can format the date as you like.

------------------------------------------------------
I suppose you could also get the text value like this too:

=TEXT(DATE(LEFT(A1,4),SUBSTITUTE(RIGHT(A1,2),".","")+1,0),"mmm-yy") EDIT: Duh...You mentioned that already in your post...

Does that work?

 

[Dave Patton]

either of the following should work
format to your preference


=(RIGHT(A6,2)&"/"&LEFT(A6,4))+0
=DATE(LEFT(A6,4),MID(A6,6,2),1)

 

[barry houdini]

Perhaps try

=TEXT(SUBSTITUTE(A1&"-01",".","-"),"mmm-yy")