asked on

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

ASKER CERTIFIED SOLUTION

Steve

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rowby Goren

ASKER

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

Steve

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... :)

SOLUTION

barry houdini