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.
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,037
Office Version
  1. 365
Platform
  1. Windows
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,037
Office Version
  1. 365
Platform
  1. Windows
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,037
Office Version
  1. 365
Platform
  1. Windows
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

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">06/04/2014</td><td style="text-align: right;;">6</td><td style="text-align: right;;">4</td><td style="text-align: right;;">2014</td><td style="text-align: right;;">04/06/2014</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">06/05/2014</td><td style="text-align: right;;">6</td><td style="text-align: right;;">5</td><td style="text-align: right;;">2014</td><td style="text-align: right;;">05/06/2014</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">06/05/2014</td><td style="text-align: right;;">6</td><td style="text-align: right;;">5</td><td style="text-align: right;;">2014</td><td style="text-align: right;;">05/06/2014</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">06/06/2014</td><td style="text-align: right;;">6</td><td style="text-align: right;;">6</td><td style="text-align: right;;">2014</td><td style="text-align: right;;">06/06/2014</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">06/07/2014</td><td style="text-align: right;;">6</td><td style="text-align: right;;">7</td><td style="text-align: right;;">2014</td><td style="text-align: right;;">07/06/2014</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">06/10/2014</td><td style="text-align: right;;">6</td><td style="text-align: right;;">10</td><td style="text-align: right;;">2014</td><td style="text-align: right;;">10/06/2014</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="color: #333333;background-color: #FAFAFA;;">6/17/2013</td><td style="text-align: right;;">6</td><td style="text-align: right;;">17</td><td style="text-align: right;;">2013</td><td style="text-align: right;;">17/06/2013</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="color: #333333;background-color: #FAFAFA;;">6/19/2013</td><td style="text-align: right;;">6</td><td style="text-align: right;;">19</td><td style="text-align: right;;">2013</td><td style="text-align: right;;">19/06/2013</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="color: #333333;background-color: #FAFAFA;;">6/19/2013</td><td style="text-align: right;;">6</td><td style="text-align: right;;">19</td><td style="text-align: right;;">2013</td><td style="text-align: right;;">19/06/2013</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="color: #333333;background-color: #FAFAFA;;">6/20/2013</td><td style="text-align: right;;">6</td><td style="text-align: right;;">20</td><td style="text-align: right;;">2013</td><td style="text-align: right;;">20/06/2013</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F1</th><td style="text-align:left">=DATE(<font color="Blue">E1,C1,D1</font>)</td></tr></tbody></table></td></tr></table><br />
 

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!
 

Watch MrExcel Video

Forum statistics

Threads
1,108,806
Messages
5,524,983
Members
409,613
Latest member
Dalex100

This Week's Hot Topics

Top