Excel EOMONTH Function
Summary
The Excel EOMONTH function returns the last day of the month, n months in the past or future. You can use EDATE to calculate expiration dates, due dates, and other dates that need to land on the last day of a month. Use a positive value for months to get a date in the future, and a negative value to get a date in the past.
Purpose
Get last day of month n months in future or past
Return value
Last day of month date
Syntax
=EOMONTH (start_date, months)
Arguments
- start_date - A date that represents the start date in a valid Excel serial number format.
- months - The number of months before or after start_date.
Usage notes
EOMONTH takes two arguments: a start date, and months. Months represents the number of months to move in the future or past. For example, with May 12, 2017 in cell B5:
You can use EOMONTH to move through years as well:
See links below for more examples of how to use the EOMONTH function in formulas.
Notes
- For months, use a positive value for future dates and a negative value for dates in the past.
Note: in Excel's date system, dates are serial numbers. January 1, 1900 is number 1 and later dates are larger numbers. To display date values in a human-readable date format, apply a the number format of your choice.
EOMONTH formula examples
To average by month, you can use a formula based on the AVERAGEIFS function, with help from the EOMONTH function. In the example shown, the formula in F4 is:
=AVERAGEIFS(amounts,dates,">="&F5,dates,...
To get the last weekday in a month (i.e. the last Saturday, the last Friday, the last Monday, etc) you can use a formula based on the EOMONTH and WEEKDAY functions.
In the example shown, the formula in D5 is:
=...
To get the first day of the month for a given date, you can use a simple formula based on the DAY function. In the example shown, the formula in cell C5 is:
=B5-DAY(B5)+1
How this formula works
The DAY function...
To calculate a retirement date based on a birth date, you can use the EDATE function.
In the example shown, the formula in D6 is:
=EDATE(C6,12*60)
How this formula works
The EDATE function is fully automatic, and...
To sum by month, you can use a formula based on the SUMIFS function, with help from the EOMONTH function. In the example shown, the formula in F4 is:
=SUMIFS(amount,date,">="&E4,date,"<=...
To count dates in the current month, you can use a formula based on the COUNTIFS or SUMPRODUCT function as explained below. In the example shown above, the formula in E7 is:
=COUNTIFS(dates,">="&...
To get the last working day in a month, you can use the WORKDAY function together with the EOMONTH function. In the example, the formula in C4 is:
=WORKDAY(EOMONTH(B4,0)+1,-1)
How this formula works
Working from the...
To generate a series of dates incremented by month, you can use the SEQUENCE function together with the EDATE function or EOMONTH function. In the example shown, the formula in D5 is:
{=EDATE(B5,SEQUENCE(12,1,0))}...
To generate a dynamic series of dates with a formula that increases by one month from a single start date, you can use a formula based on DAY, MONTH, YEAR, and DATE functions.
How the formula works
In the example, B6...
To get the number of days in a given month from a date, you can use a formula based on the EOMONTH and DAY functions.
In the example shown, the formula in cell B5 is:
=DAY(EOMONTH(B5,0))
How this formula works
The...
To sum by month in columns you can use the SUMIFS function together with the EOMONTH function. In the example shown, the formula in G5 is:
=SUMIFS(amount,client,$F5,date,">="&G$4,date,"<=...
To calculate an expiration in the future, you can use a variety of formulas. In the example shown, the formulas used in column D are:
=B5+30 // 30 days
=B5+90 // 90 days
=EOMONTH(B7,0) // end of month
=EDATE(B8,1) //...
To average response times by month, you can use a formula based on the AVERAGEIFS function, together with the EOMONTH function.
In the example shown, the formula in G5 is:
=AVERAGEIFS(durations,dates,">=...
To calculate the last day of a month based on a given date, you can use the EOMONTH function. In the example shown, the formula in cell B5 is:
=EOMONTH(B5,0)
How this formula works
The 2nd argument (months) of the...
To count new customers by month, you can use a helper column and the COUNTIFS function. In the example shown, the formula in H5 is:
=COUNTIFS(new,1,date,">="&G5,date,"<="&EOMONTH(G5,0...
Related videos
How to generate an expiration date in the future, how to calculate "days remaining", and how to highlight dates that are expired or expiring soon with conditional formatting.
Related functions
Good links
