Solved

I'm trying to format numbers in an excel column to a date format.

Posted on 2011-02-12
3
209 Views
Last Modified: 2012-05-11
I have a column of numbers starting in cell A1 and ending in A29163.  The all have 8 numbers in the format 19000103   The first 4 numbers are the year, the next 2 are the month and the last 2 are the day.  I would like the cell to show the date format as 1900-Jan-03     Can anyone please help me automate this with a vba script to do it in one shot? Thanks
0
Comment
Question by:dmalovich
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst earned 500 total points
ID: 34880606
Hello,

with a formula you could use

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

then format the result with custom format yyyy-mmm-dd

cheers, teylyn
0
 
LVL 45

Expert Comment

by:patrickab
ID: 34880616
>Can anyone please help me automate this with a vba script to do it in one shot?

It is always much faster to use Excel's built-in formulae rather than use VBA. So teylyn's solution is the best way to go.

Patrick
0
 

Author Closing Comment

by:dmalovich
ID: 34880678
Awesome. Thanks......
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

697 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question