[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More


Ms Excel MONTH function madness

Published on
8,296 Points
3 Endorsements
Last Modified:
Here's a very little known use of the commonly used Excel function MONTH which in it's essence returns the month number of the date or serial number it is given.

I guess we are all familiar with the MONTH function in Excel but let us take some time to go through the process. If you give it a date, well not the date exactlly but a serial number that belongs to a certain date, it will return the whole number value corresponding to that month. So very important, it takes a number (serial number belonging to the date) and it returns a number. So far so good. Now here's the strange thing…

I will use an example to describe this. 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. Now some might say VLOOKUP, add a second table of a number series from 1 to 12 and the corresponding month names in the second column. Now they would be spot on but here what you can also get the same result. You can write the following function =MONTH("1"&A1) and as you press enter, and just like that, you get 11. Wow!
 MONTH function madnessThe reason why this works is the same reason for which the Excel will return a date if you write 1November in a Cell. It will understand that as the 1st of November in the current year, or a numeric value corespondidng to that date so the MONTH function has no problem returning the month number.

But since the 19March (for Example)  is not a very commonly used  way for a date input in Excel, it is not considered (or simply nobody knows) that you can actually use various date functions with this kind of date input.

This is just an example of the MONTH function but all other date function would work just as well.
  • 2
LVL 74

Expert Comment

The reason is that you can always enter a string like "31March" or "31Mar2013", and it will be converted into a date. If you use =Month(31 & A1) with your November (string) value, you'll see an error.
LVL 74

Expert Comment

Good find, that might get handy!

Featured Post

Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
A query can call a function, and a function can call Excel, even though we are in Access. This is Part 2, and steps you through the VBA that "wraps" Excel functionality so we can use its worksheet functions in Access. The declaration statement de…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month