Need help formatting a Date in Excel

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
mainrotorAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Steven HarrisConnect With a Mentor PresidentCommented:
=CONCATENATE(MID(B4,5,2),"-",RIGHT(B4,2),"-",LEFT(B4,4))

Substitute B4 for the cell where the date is.

EDIT
They really need to have auto-refresh of pages for new posts...
0
 
pony10usCommented:
Try this:

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

Open in new window

0
 
pony10usCommented:
:)  true but at least you added the comment about substitution that I didn't have.
0
 
Jerry PaladinoCommented:
=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.
0
 
Rob HensonFinance AnalystCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.