A common question in my Excel classes is “how do I automatically calculate the last day of each month?” For many people this represents pay-day or invoice-delivery day. So here goes…

Of course, you could just type the dates in yourself but apart from the time it takes, you’d have to remember how many days each month has as well as whether it’s a leap year or not.

In the screenshot below, column B contains formulas to calculate the last day of the appropriate month from column A.

The formula it uses the EOMONTH function (end of month) which requires 2 arguments:

First Argument

The first argument is a date. Not just a month name but a proper date (or a cell containing a proper date or a formula that generates a date).

In this example, I only have the month name, so I’ve used a formula to generate a date (to keep it simple, the formula generates a date that is the first day of the month).

The formula in B2 is “1-” & A2 &”-” & YEAR(TODAY())

Which means join the text “1-” to what is in A2 (the word January) to a “-” to the year part of today’s date. This results in 1-January-2014.

Note 1: I could have used a slash (/) instead of hyphen (-)

Note 2: I used Year(Today()) so that the spreadsheet can be re-used every year. The TODAY() function calculates the current date and the YEAR function extracts the year from a specified date.

Second Argument

The second is a number which represents the number of months from the first argument date. So for example…

If the second argument is 0, the result of the EOMONTH function will be the last day of the month specified in the first argument.

If the second argument is 1, the result of the EOMONTH function will be the last day of the month one month after the one specified in the first argument (e.g. If the month of the first argument is March, the result of the EOMONTH function will be 30 April)

and so on

So in this case the value of the second argument will always be 0.

The complete function in B2 is =EOMONTH(“1-” & A2 &”-“& YEAR(TODAY()),0)

This can then be copied down column B.

The formula generates a “serial number” – every day is assigned a unique number by Excel – this is actually the number of days since 1st January 1900 (the first date that Excel recognises).

To convert the serial numbers to something more recognisable, select the cells B2:B13 and apply a date format to them.