Excel Unplugged

MONTH function madness in Excel

I guess we are all familiar with the MONTH function in Excel. If you give it a date as an argument, it will return the month number. So far so good. But here is a use of the MONTH function you might not be used to…

If for example cell A1 contains a name of a month (let’s say November) and you want to get the month number out of it (in the given case 11). Now some might say VLOOKUP and they would be spot on but here what you can also do. You can say

 =MONTH("1"&A1)

and you get 11. Wow!

MONTH function in Excel

Try it if you don’s belive it.

Here’s why this works. If you write 1November in a cell, it is recognized by Excel as a date of the current year. So the MONTH function in Excel still recieves a date just as it is expecting and therefor can return the month number. A great little trick but nobody really knows this 🙂