MS-Excel / Functions and Formula

Using Excel Formulas

Let's suppose A1 houses the date 23-Jun-2007 and you want to have Excel reference this cell and return the date of the last day of June 2007. This following function returns 30-Jun-2007:

=DATE(YEAR(A1),MONTH(A1)+1,0)

This is because the function adds one month to the date in A1 (June becomes July) and uses 0 for the day, which forces Excel to return the last day of the previous month.

Or, you can hardcode the date as shown here:

=DATE(YEAR("22-Jun-2007"),MONTH("22-Jun-2007")+1,0)

Again you will get the result 30 June 2007.

[Contents] [Next]

In this tutorial:

  1. Find the Last Day of Any Given Month
  2. Using Excel Formulas
  3. Using EOMONTH
  4. Using a Custom Function