There are many instances where Excel users may want to return the first or last day of the month from a cell referencing a date. This can include creating additional mapping so that formulas work using one date as a criteria rather than a ranges of dates, and being able to summarise data in a more simple way.

The two functions that make easy work of this task is the Excel DAY and EOMONTH functions.

EOMONTH can be used to find the last day of a given month of a cell reference, or the last day of a relative month to that reference.

Syntax

• =EOMONTH(date,months)

The general formula would appear as below, which is asking for the last day of the date referenced in cell B3.

• =EOMONTH(B3,0)

Explanation

The examples below show the results of changing the second argument in the formula.

To return the first month of a referenced cell the formula can be manipulated to find the last day of the previous month and add one day.

• =EOMONTH(B10,-1)+1

EOMONTH can be combined with the TODAY function to return the last day of the current month.

• =EOMONTH(TODAY(),0)

 Using additional functions can increase the effectiveness and automation if formulas.

Usages

EOMONTH is simple and highly functional formula. Whether it is grouping all transactions within the month to one singular date to make a summary of the data easier, or to calculate expiry dates of memberships, vouchers or loyalty points, the EOMONTH formula can be frequently used.