• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 369
  • Last Modified:

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
0
mainrotor
Asked:
mainrotor
1 Solution
 
Steven CarnahanNetwork ManagerCommented:
Try this:

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

Open in new window

0
 
Steven HarrisPresidentCommented:
=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
 
Steven CarnahanNetwork ManagerCommented:
:)  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.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now