Solved

Need excel formula to convert a non-standard date format to another format

Posted on 2013-01-25
5
747 Views
Last Modified: 2013-01-25
Hello

I have a column of "dates" that are in this format:

Mar-11-1969  
Mar-07-2002  
Mar-21-1999  

I am looking for a formula that will change the date to (in bold)

Mar-11-1969  031169   (MMDDYY)
Mar-07-2002  030702   (MMDDYY)
Mar-21-1999  032199   (MMDDYY)

On a side note:  I have found that when I convert excel files with dates that start with a leading zero (as in 03) Excel will strip the leading zero from the date when exporting to a csv format.   (I don't need date format, so if there is another format that will NOT strip out the leading zero  (when exporting to csv) that would be great.)

Note:  FYI Next month I will have a similar column that has this date format:
Apr-21-1999

So in the formula you give me I will replace the Mar with Apr.

Hmmm... As I prepare this question, I am thinking it is a pretty simple question --  and it will expose me for the Excel ignoramus I am.  

Rowby
0
Comment
Question by:Rowby Goren
  • 2
  • 2
5 Comments
 
LVL 24

Accepted Solution

by:
Steve earned 465 total points
Comment Utility
with the date in A1 the following formula should work:

=DATEVALUE(MID(A1,FIND("-",A1)+1,2) & " " & LEFT(A1,FIND("-",A1)-1) & " " & RIGHT(A1,4))

then use =TEXT(B1,"mmddyy") to get to the desired format :)

Single formula:
=TEXT(DATEVALUE(MID(A1,FIND("-",A1)+1,2) & " " & LEFT(A1,FIND("-",A1)-1)& " " &RIGHT(A1,4)),"mmddyy")
Date-example.xlsx
Date-example.xlsx
0
 
LVL 9

Author Comment

by:Rowby Goren
Comment Utility
Works great.   Just wondering how does the formula know it's Apr or Mar (or Jun) for that matter.  Are 3 letter Months "Apr" and "Mar" a "standard" excel "parameter"?

Rowby
0
 
LVL 24

Expert Comment

by:Steve
Comment Utility
The formula converts your date to a more Excel freindly text string "dd mmm yyyy" where it can recognise the month as it is between a two digit and 4 digit number.
Then is just about re-formatting that back the way you want.

Excel will recognise 12 May 2010... but not May 12 2010... :)
0
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 35 total points
Comment Utility
>Excel will recognise 12 May 2010... but not May 12 2010

so you can use this formula to switch from one to the other and then do that conversion, i.e.

=TEXT(REPLACE(MID(A1,5,7),4,0,LEFT(A1,4)),"mmddyy")

or you can just extract the relevant elements directly like this

=TEXT(1&LEFT(A1,3),"mm")&MID(A1,5,2)&RIGHT(A1,2)

both should produce the required results

regards, barry
0
 
LVL 9

Author Closing Comment

by:Rowby Goren
Comment Utility
Thanks The_Barman and barryhoudini.  

For the solution and the explanations.  

Rowby
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
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…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

762 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now