A to Z of Excel Functions: The MONTH Function

For some working in financial modelling, it appears that MONTH may be the closes they get to a date (groan – Ed.).  Seriously, working with time series in Excel is an important part of financial modelling.  Since dates in Excel are merely formatted serial numbers counting from 1 January 1900 (usually), being able to identify the month from a date is not as trivial as you may first think.

Therefore, the MONTH function returns the month of a date by a serial number, from 1 (January) to 12 (December).  It employs the following syntax to operate:

The MONTH function has the following argument:

• serial_number: this is required and represents the date of the month you are trying to find. Dates should be entered by using the DATE function or as the results of other formulae or functions.  For example, you should use DATE(2008,8,17) for the 17^th August 2008.  Problems may occur if dates are entered as text.

It should be further noted that:

• Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations.  By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900
• values returned by the YEAR, MONTH and DAY functions will be Gregorian values, regardless of the display format for the supplied date value.  For example, if the display format of the supplied date is Hijri, the returned values for the YEAR, MONTH and DAY functions will still be values associated with the equivalent Gregorian date.

Please see my example below:  

There is an alternative to this approach, depending upon:

• what you require
• accept that the result will be returned as a text data type, even if it has the appearance of a number.

The TEXT function allows you to change the way a number appears by applying formatting to it with format codes.  It's useful in situations where you want to display numbers in a more readable format or you want to combine numbers with text or symbols.

However, as mentioned above, the TEXT function will convert numbers to text, which may make it difficult to reference in later calculations.  It’s best to keep your original value in one cell, then use the TEXT function in another cell.  Then, if you need to build other formulae, always reference the original value and not the TEXT function result.

Its syntax is as follows:

The TEXT function has the following arguments:

• value_to_be_formatted: this is required and represents the value to be reformatted
• “format_code”: this is required and must be in quotation marks.  This represents the custom number formatting required.

For months formats, the following customer number format codes are relevant:

We’ll continue our A to Z of Excel Functions soon.  Keep checking back – there’s a new blog post every other business day.

A full page of the function articles can be found here.