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

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
LVL 9
Rowby GorenAsked:
Who is Participating?
 
SteveConnect With a Mentor Commented:
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
 
Rowby GorenAuthor Commented:
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
 
SteveCommented:
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
 
barry houdiniConnect With a Mentor Commented:
>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
 
Rowby GorenAuthor Commented:
Thanks The_Barman and barryhoudini.  

For the solution and the explanations.  

Rowby
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.