<div>
Are row 1 items from columns X and on also dates?<br />
<br />
This works for columns E to W only:<br />
Open your xls.<br />
Save as .txt.<br />
Open the .txt. The Import wizard should start.<br />
Pick Comma delimiter.<br />
Change Column data format to YMD.<br />
Scroll to right and select columns E to W.<br />
Finish.
</div>


<div>
Is that format YYMMDD? If so, use this formula...<br />
<br />
&nbsp; &nbsp; &nbsp;=DATE(LEFT(A1, 2)+100, MID(A1, 3, 2), RIGHT(A1, 2))<br />
<br />
...where A1 contains your date.
</div>


<div>
If it's VBA you are after, select the cells to modify then run this macro...<br />
<br />

<pre><code class="code-10 nolinks" id="code-20-40438859-1"><span>Sub ChangeDateFormat()</span>
<span></span>
<span>    Dim cell As Range</span>
<span>    For Each cell In Selection</span>
<span>        If Len(cell) = 6 Then</span>
<span>            cell = DateSerial(Left(cell, 2), Mid(cell, 3, 2), Right(cell, 2))</span>
<span>        End If</span>
<span>    Next</span>
<span>    </span>
<span>End Sub</span>
</code></pre>
</div>


<div>
So the month is the last 2 digits on the right? &nbsp;<br />
<br />
Does that make sense for column F value 141113? &nbsp;That would be month 13, or Jan 11, 2015? &nbsp;Is that what you are saying? &nbsp;<br />
<br />
Also what is with 141201-49-W &quot;date&quot; starting in Column X? &nbsp;Thanks.
</div>


<div>
Thanks a lot for all the comments, I will try them out when I get to work. Regarding Tom's question about &quot;&nbsp;...Also what is with 141201-49-W &quot;date&quot; starting in Column X?&quot; &nbsp;The week number gets added once the dates get so far out. And I should have pointed out that the dates are European not American so the last two digits of the block of six are the days and the middle two are the month
</div>


<div>
Thanks a lot to all that contributed, but Kris gets the gold star. I can 'trim' the cells that contain week numbers before I run the macro.<br />
Thanks again for your time
</div>


<div>
<div class="content wysiwyg-content">
Hi,<br />
I get emailed a workbook each week with updated figures that we have to ship to the customer.<br />
Problem is, that the dates in row 1 have the following format '141112' etc etc. meaning 12/11/14 and so on.<br />
Is there a formula or maybe vba code that I could use that would change the dates around to read 12/11/14 etc instead of 141112 ?<br />
I have attached the workbook in question.<br />
Your help as always is much appreciated.<br />
<a href="https://filedb.experts-exchange.com/incoming/2014/11_w46/882731/wk-46.xlsx" target="_blank" class="file-inline" title="Sheet 1 has the data (14 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>wk-46.xlsx</a>
</div>
</div>


wk-46.xlsx

Hi,
I get emailed a workbook each week with updated figures that we have to ship to the customer.
Problem is, that the dates in row 1 have the following format '141112' etc etc. meaning 12/11/14 and so on.
Is there a formula or maybe vba code that I could use that would change the dates around to read 12/11/14 etc instead of 141112 ?
I have attached the workbook in question.
Your help as always is much appreciated.

Changing date format in row one

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