Turning text into a date

[entiendo]

Hi all

I'm looking for some help on preparing a formula to essentially make a text column into a date. At present my columns are like this as outputted from accounts software:

Date
3-1-2011
7-1-2011
7-1-2011
7-1-2011
31-10-2011
30-11-2011
31-12-2011

As you can see there's no uniform way of them being presented and the problem complicates further when you have double digit days and months.

I've tried using the RIGHT, LEFT & MID functions to no avail. I've also tried excel's formatting functions but it can't automatically change the text into a date. I've tried the various help menus but just cannot find a solution.

The reason I'm doing this is so I can use Pivot tables and then group by month but as it's text I can't do that at the moment.

Any help is appreciated.


Matt

[vlady]

did you tried text to column?

outputted from accounts software

[entiendo]

I have but because of the double digits on days and months in certain parts the split doesn't work, essentially it's because of the length changes depending on the date.

[vlady]

i mean
text-to-column---choose "delimeted"
-click "next"
-choose "date"
-choose format in the drop down e.g. MDY
-then "finish" that will convert them to date.

[entiendo]

That has sorted it, many thanks for your help! I didn't realise you could specify hyphens (-) with this function.

[vlady]

Your welcome.