A trick with MONTH() Function in Excel

032913_1042_MONTHfuncti1.png

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’t believe 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 receives a date just as it is expecting and therefore can return the month number. A great little trick but nobody really knows this ?

4 thoughts on “A trick with MONTH() Function in Excel

Leave a Reply

Your email address will not be published. Required fields are marked *