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:
=DATE(D3, SEQUENCE(12,1,1,1), 1)Check our detailed blog on calculating the starting dates of every month.
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:
=EOMONTH( DATE( D3, SEQUENCE(12,1,1,1), 1), 0)
That’s it! This will return all the ending dates of a given year:
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:
