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

Marts81

New Member
Joined
Feb 19, 2014
Messages
10
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.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,142
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Try using Text to Columns
 

Marts81

New Member
Joined
Feb 19, 2014
Messages
10
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

Well-known Member
Joined
Jan 11, 2012
Messages
4,142
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Could you pot some sample data as they appear?
 

Marts81

New Member
Joined
Feb 19, 2014
Messages
10

ADVERTISEMENT

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, thanks6/22/2013


<tbody>
</tbody>
 

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,142
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
BCDEF
106/04/201464201404/06/2014
206/05/201465201405/06/2014
306/05/201465201405/06/2014
406/06/201466201406/06/2014
506/07/201467201407/06/2014
606/10/2014610201410/06/2014
76/17/2013617201317/06/2013
86/19/2013619201319/06/2013
96/19/2013619201319/06/2013
106/20/2013620201320/06/2013
Sheet1
Cell Formulas
RangeFormula
F1=DATE(E1,C1,D1)
 

Marts81

New Member
Joined
Feb 19, 2014
Messages
10
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!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,484
Messages
5,831,933
Members
430,094
Latest member
soconfused365

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top