<div>
Try this:<br />
<br />

<pre><code id="code-20-39755154-1">=CONCATENATE(MID(A1,5,2),&quot;-&quot;,RIGHT(A1,2),&quot;-&quot;,LEFT(A1,4))</code></pre>
</div>


Try this:



=CONCATENATE(MID(A1,5,2),"-",RIGHT(A1,2),"-",LEFT(A1,4))

<div>
:) &nbsp;true but at least you added the comment about substitution that I didn't have.
</div>


:)  true but at least you added the comment about substitution that I didn't have.

<div>
=DATEVALUE(MID(A1,5,2) &amp;&nbsp;&quot;-&quot; &amp;&nbsp;RIGHT(A1,2) &amp;&nbsp;&quot;-&quot; &amp;&nbsp;LEFT(A1,4))<br />
<br />
Converts the text to an Excel serial date. &nbsp; &nbsp; (41213 for 10-31-2012)<br />
<br />
Then use Format/Cells/Number and pick the mm-dd-yyyy format for the cell(s). &nbsp;Or any other date format that you might want.
</div>


=DATEVALUE(MID(A1,5,2) & "-" & RIGHT(A1,2) & "-" & LEFT(A1,4))

Converts the text to an Excel serial date.     (41213 for 10-31-2012)

Then use Format/Cells/Number and pick the mm-dd-yyyy format for the cell(s).  Or any other date format that you might want.

<div>
Will your date string always be 8 characters?<br />
<br />
What happens when you have a single digit month and/or single digit day?<br />
<br />
Just a couple of points to consider.<br />
<br />
Thanks<br />
Rob H
</div>


Will your date string always be 8 characters?

What happens when you have a single digit month and/or single digit day?

Just a couple of points to consider.

Thanks
Rob H

<div>
<div class="content wysiwyg-content">
Hi Experts,<br />
I have a PostDate column on a spreadsheet that has the following format: 20121031<br />
<br />
The first four characters are the year, characters 5 and 6 are for the month, and characters 7 and 8 are for the day value.<br />
<br />
I need to create a new column that copies the values from the values in the PostDate column, but uses the following date format 10-31-2012. &nbsp;How can i do this? &nbsp;<br />
<br />
Thanks in advance,<br />
mrotor
</div>
</div>


Hi Experts,
I have a PostDate column on a spreadsheet that has the following format: 20121031

The first four characters are the year, characters 5 and 6 are for the month, and characters 7 and 8 are for the day value.

I need to create a new column that copies the values from the values in the PostDate column, but uses the following date format 10-31-2012.  How can i do this?  

Thanks in advance,
mrotor

Need help formatting a Date 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.

Microsoft Excel

Microsoft Office is an integrated suite of applications that includes Outlook, Word, Excel, Access, PowerPoint, Visio and InfoPath, along with a number of tools to assist in making the individual components work together. Coding within and between the projects is done in Visual Basic for Applications, known as VBA.