Change the written name of a month into its numerical equivalent in Excel

Steve_Brady
Steve_Brady used Ask the Experts™
on
Hello,

Is there a formula in Excel (2010) that will change the written name of a month into its numerical equivalent?

I frequently run across date entries in which the year and day are specified by numbers but the month is specified by a word. For example, the following is from the Date&Time column in a database of recorded events from a webpage:
...
May 20, 2012 7:55:14 PM          >>>          05/20/12
May 20, 2012 2:26:33 PM          >>>          05/20/12
May 20, 2012 9:33:20 AM          >>>          05/20/12
May 19, 2012 9:47:38 AM          >>>          05/19/12
May 18, 2012 11:57:02 PM          >>>          05/18/12
May 18, 2012 11:52:19 PM          >>>          05/18/12
May 18, 2012 12:42:01 PM          >>>          05/18/12
...

The long entries on the left reveal the format of the original data and the familiar 2-slash dates on the right display the desired format with the time discarded.

The following code effects the conversion:

=DATE(
    MID(A1,SEARCH(",",A1)+2,4),
    IF(LEFT(A1,3)="Jan",1,
        IF(LEFT(A1,3)="Feb",2,
        IF(LEFT(A1,3)="Mar",3,
        IF(LEFT(A1,3)="Apr",4,
        IF(LEFT(A1,3)="May",5,
        IF(LEFT(A1,3)="Jun",6,
        IF(LEFT(A1,3)="Jul",7,
        IF(LEFT(A1,3)="Aug",8,
        IF(LEFT(A1,3)="Sep",9,
        IF(LEFT(A1,3)="Oct",10,
        IF(LEFT(A1,3)="Nov",11,
        IF(LEFT(A1,3)="Dec",12)))))))))))),
    MID(A1,SEARCH(" ",A1)+1,SEARCH(",",A1)-1-SEARCH(" ",A1))
    )

Open in new window

However, it would certainly be a lot simpler and cleaner if there was a single function, etc., for the Month argument.  In fact, for something so common, I anticipate there is (a function) and I have just not yet learned it.  

In if that is not the case however, feel free to copy the following to save in your bag of shortcuts for the next time you need it.

=IF(LEFT(A1,3)="Jan",1,
IF(LEFT(A1,3)="Feb",2,
IF(LEFT(A1,3)="Mar",3,
IF(LEFT(A1,3)="Apr",4,
IF(LEFT(A1,3)="May",5,
IF(LEFT(A1,3)="Jun",6,
IF(LEFT(A1,3)="Jul",7,
IF(LEFT(A1,3)="Aug",8,
IF(LEFT(A1,3)="Sep",9,
IF(LEFT(A1,3)="Oct",10,
IF(LEFT(A1,3)="Nov",11,
IF(LEFT(A1,3)="Dec",12))))))))))))

Open in new window

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2012
Commented:
This will give you the month number, and alleviate all the nested if's:

=MATCH(LEFT(A1,3),{"JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC"},0)

Cheers,

Dave
Commented:
Try this: =DATEVALUE(Your Date Here)

=DATEVALUE(A1)

WHERE CELL A1 = May 20, 2012 7:55:14 PM

Commented:
Then you can just format the cells, select Custom and use mm/dd/yy
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Most Valuable Expert 2013
Commented:
Hello Steve,

I second nito8300's approach. In general as long as the date/time string is in a recognisable date format for your location (regional settings) DATEVALUE should work.

For converting months as text to numbers, if you had just the month name like Aug or August in A1 then this formula in B1 will convert to the month number

=MONTH(1&A1)

concatenating a 1 to the month name gives you a string which excel interprets as a date in the current year, so if A1 = August

1&A1 = 1August = 1 August 2012

so month function will read that as a date and return the result 8

regards, barry
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
Depending on where your dates come from, you may need to convert non-breaking spaces (ASCII 160) into real spaces before you can convert them.
=INT(SUBSTITUTE(A1,CHAR(160)," "))

The INT function removes the time from the date/time text literal. The SUBSTITUTE function replaces the ASCII 160 characters with a space.

Brad
Most Valuable Expert 2012
Top Expert 2012

Commented:
barry, will =Month(1&A1) work with 3 character month names, as well?

Dave
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
Dave,
Did it not do so in your tests?

It did in mine.

Brad
Most Valuable Expert 2012
Top Expert 2012

Commented:
Thanks for that, Brad, lol.

Neat stuff.  Amazing the backflips you guys come up with.  (by backflips, I mean the stuff that's not obvious).

Cheers,

Dave

Author

Commented:
Great responses.  Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial