Have you ever wanted to convert a Month name to its serial number in Microsoft Excel, or for that matter OpenOffice.org Calc (I have just tested and it works)? For example, Converting February to 2. Unfortunately, MONTH() expects an actual date; MONTH("January"), for example, won’t convert but MONTH("14-Jan-08") will. For a report I was recently writing, I had to figure out a way to overcome this problem. Finally, I found that if a number was joined to the month name a serial number would be returned (although the text passed to month was in no way a date format recognised by Excel’s formatting).
The solution was as follows:
In cell F2 I had the month name (which was changeable due to a drop-down list). So I used =MONTH(1&F2) which returned the month’s number. If only Excel would simply accept a month also … similar to WEEKDAY() (which accepts the day and returns the day number).
So there you go, a simple way to convert the month name to the corresponding serial number in Microsoft Excel/OpenOffice.org Calc.
No related posts.
Rock-N-Roll, Bro! Even 1 year later. Thanks
Thanks! I inheritied support of a boatload of financial statements and this is exactly what I was looking for! I’ve been using Excel tricks for 10 years but didn’t think of this one. Makes sense tho!
You’re welcome guys. I’m happy other people else found it useful, too.
Best wishes,
-Paul
Just found this via a Google search. Does exactly what I needed to do! Nice one.
Nice one mate. Been looking for an elegant solution for a while and before I found your post I came up with:
=TEXT(1 & ” ” & B1 & ” ” & 2000, “mm”)
with the month string in B1
I also wanted the number as 2 digits e.g january = 01
Much prefer the method you came up with.
Cheers,
Mark
Very neat. Had struggled for ages to find such an elegant way to do this.
Thanks
I have been trying to figure this out and your suggestion did the trick.
Thanks a lot.
Melinda
thanks mate! Very helpful tip. Now i just need to figure out how to convert the number to a month. Im sure its there somewhere – just cant find it!!
Fantastic! no idea HOW it workls though which bugs me
Thank you
Brilliant, thanks. Also no idea why this should work but glad it does!
Thanks very much – exactly what I was looking for!
Perfect!
Thanks for this solution!
Easy to understand and just what I needed. Thanx heeps. Keep posting!
-SG
Nice! We rather appreciated the website
Brilliant! How did you find this out?
hi Mitch,
I had tried quite a few different ways of trying to produce a number using text but no joy. So I thought, “What if I make the month a date?” This worked. The ampersand (&) joins the month name with 1 and this makes it a date (for example, 01/03/2009 if the month is January) then month converts is to a number.
Regards.
Thanks a lot
Great info, precisely what I was looking out for. Thank you very much
This is an interesting approach, but wouldn’t quite work for me since I could not introduce a drop-down (the solution returns the index of the drop-down).
If drop-down is not an option, you can use the MATCH function.
For example, if B33 contains one of “JAN”, “FEB”, etc, the formula:
MATCH(B33,{“JAN”,”FEB”,”MAR”,”APR”,”MAY”,”JUN”,”JUL”,”AUG”,”SEP”,”OCT”,”NOV”,”DEC”},0)
Will return 1 for “JAN”, etc.
Extends easily for full month names (change “JAN” to “January” in the formula).
Brilliant, many thanks.
Thanks for your share.
It’s great!
Thanks dude, It’s Brilliant!
thanks, just what I was looking for
Awesome!!! Thanks
Strange; it only seems to work for me for the months April, August, September, November, December. For the other months Excel yells #VALUE! at me…. Any ideas…?
OK, nevermind, seems to be a language thing… Thanks!