Example EOMONTH

A useful date function for reports showing more than one month is the EOMONTH function. 

 

The EOMONTH function calculates the end of month date based on a date in another cell.  So those users who have reports with many tabs that they manually update every year to a new 12 months need only change one cell if you use the EOMONTH function!  

 

A basic way of extending a date range is to enter a couple of dates and then copy the dates for the needed number of months like below.

But what if these dates change over multiple tabs? If you use the EOMONTH function all you need to do is update the first cell (C2 example below) each year to change an entire sheet or workbook.

EOMONTH(start_date, no. of months forward or backward) 

The example above shows how the result returned from just the EOMONTH function would be 31 Jan 15 but by adding a +1 to the end adds 1 day to make the result 1 Feb 15.  If cell C2 value was 31 Jan 15 (not 1 Jan 15) the formula =EOMONTH(C2,1) would be used to return a date 28 Feb 15.  The function can be used in reverse by using negative months.

Please feel free to download the example above.  I hope you find this post useful. 

Darren