Imported dates formatted as general won't turn into date format

[Marts81]

Hello,

I've imported some data from Text and Access. While Access data seems fine, SOME of the dates imported from text are formatted as general and won't turn into date even if I copy them and paste special on another column. So some of them are stuck on the left of the cell and I would like to do a pivot table using all of them. I've tried to use the formula Datavalue while converting them into text but it doesn't work.

Please let me know if you come out with other solutions. They will be highly appreciated.

Thanks a lot.

 

[Momentman]

Try using Text to Columns

 

[Marts81]

Thank you Momentman.
I've done it setting it up as DMY.
But they are still on the left of the cell formatted as dates now. I used the date function to see what comes up and it return me a VALUE error.

 

[Momentman]

Could you pot some sample data as they appear?

 

[Marts81]

	06/04/2014
	06/05/2014
	06/05/2014
	06/06/2014
	06/07/2014
	06/10/2014
	6/17/2013
	6/19/2013
	6/19/2013
	6/20/2013
Here is some of the data, thanks	6/22/2013

<tbody>
</tbody>

 

[Momentman]

Maybe, do a Text to Columns using "/" as the delimeter, that way you have the data broken into 3 cells and then you can use the DATE function to bring them back together like

Excel 2010
	B	C	D	E	F
1	06/04/2014	6	4	2014	04/06/2014
2	06/05/2014	6	5	2014	05/06/2014
3	06/05/2014	6	5	2014	05/06/2014
4	06/06/2014	6	6	2014	06/06/2014
5	06/07/2014	6	7	2014	07/06/2014
6	06/10/2014	6	10	2014	10/06/2014
7	6/17/2013	6	17	2013	17/06/2013
8	6/19/2013	6	19	2013	19/06/2013
9	6/19/2013	6	19	2013	19/06/2013
10	6/20/2013	6	20	2013	20/06/2013
Sheet1

Cell Formulas
Range	Formula
F1	=DATE(E1,C1,D1)

 

[Marts81]

Thanks so much for your help Momentman.
I've been able to solve the problem by using "/" as delimeter. The result was really awkward though on my spreadsheet. It returned me 2004 as 20-Apr since formatted as date.
I left it as text, concatenate the cell using &"/" and then turn them into date with datevalue......
A bit of working around but I've managed.

Thanks a lot again!