<div>
Insert a new column next to that column (column J) and insert the following formula and copy down:<br />
<br />
=RIGHT(I2,2)&amp;&quot;/&quot;&amp;MID(I2,5,<wbr />2)&amp;&quot;/&quot;&amp;LEF<wbr />T(I2,4)<br />
<br />
And then Copy this new column (J) and Past Special and select &quot;Values&quot; at Column I.
</div>


Insert a new column next to that column (column J) and insert the following formula and copy down:

=RIGHT(I2,2)&"/"&MID(I2,5,2)&"/"&LEFT(I2,4)

And then Copy this new column (J) and Past Special and select "Values" at Column I.

<div>
Try using &quot;Text to columns&quot; functionality to convert without an extra column.<br />
<br />
Select column of dates then use<br />
<br />
Data &gt;&nbsp;Text to columns &gt;&nbsp;Next &gt;&nbsp;Next &gt;&nbsp;at step 3 choose &quot;Date&quot; option under &quot;column adta format&quot; and then &quot;YMD&quot; from the dropdown &gt;&nbsp;OK<br />
<br />
Now you can format as required<br />
<br />
regards, barry
</div>


Try using "Text to columns" functionality to convert without an extra column.

Select column of dates then use

Data > Text to columns > Next > Next > at step 3 choose "Date" option under "column adta format" and then "YMD" from the dropdown > OK

Now you can format as required

regards, barry

<div>
sorry that should be &quot;column data format&quot;.....
</div>


sorry that should be "column data format".....

<div>
<div class="content wysiwyg-content">
Hi,<br />
I have a number representing an expiry date that is sent from a supplier in a CSV file. &nbsp;For example 20130228 represents and expiry date of 28/02/2013. &nbsp;When I try and format this to a date in excel all I get is a load of hash marks (see screenshot) <a href="https://filedb.experts-exchange.com/incoming/2011/03_w12/429777/screenshot.JPG" target="_blank" title="Screenshot of the cell once it has been formatted as a date. (64 KB)"><img alt="Screenshot of the cell once it has been formatted as a date." class="file-block lazyload" style="max-width: 800px;" data-src="https://filedb.experts-exchange.com/incoming/2011/03_w12/800_429777/screenshot.JPG" /></a><br />
How do I get this data into an excel date format?
</div>
</div>


Screenshot of the cell once it has been formatted as a date.

screenshot.JPG

Hi,
I have a number representing an expiry date that is sent from a supplier in a CSV file.  For example 20130228 represents and expiry date of 28/02/2013.  When I try and format this to a date in excel all I get is a load of hash marks (see screenshot) 

How do I get this data into an excel date format?

Changing numbers to dates in excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.