How to Calculate the Ending Dates of Months in Excel?

This post will show you how to use some powerful Excel functions like DATE, EOMONTH and SEQUENCE to calculate the ending dates of every month. Read along to calculate these with a single formula, in no time.

For our example, let us assume the user input of a year in the cell D3:

Before we get to the ending date of every month, we’ll get the starting date of every month using the below formula:

Note: The SEQUENCE function is available in Excel 2021 or the Excel 365 Subscription

The DATE function, given a year, month, and day this function returns the corresponding date.

While, the SEQUENCE function, returns a series of numbers in given rows and columns, based on the starting value and increments.

By using the start of the month function that we just derived, we will work to get to the end of the month by using the EOMONTH function.

Syntax for which is, 

for the second argument, +ve, move to the future months, if a -ve, move to the past months, if 0 then return end of month of the same month. 

Since we need the end of the same month, our formula would be:

That’s it! This will return all the ending dates of a given year:

A screenshot of a calendar

Description automatically generated

We have a dedicated YouTube video on calculating the starting, ending dates and number of days of every month for a given year, check the same here:

Leave a Reply

Your email address will not be published. Required fields are marked *