How to Calculate the Starting Dates of Months in Excel?

In this post, we’ll learn how to use the powerful DATE, and SEQUENCE functions to get the starting dates of every month, given a year.

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

How do we get starting dates for the given year?

Let us use the DATE function combined with SEQUENCE function in Excel to generate the first day of each month.

Understanding the formula with its syntax, first, what does DATE function return?

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

What will the SEQUENCE function take in as arguments and return?

return a series of numbers in given rows and columns, based on the starting value and steps to increment from the starting value 

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

In our formula, the Sequence function returns 12 rows and a single column of data starting from 1 and incrementing 1 in each step. This is for the 12 months in any year, like this:

A white rectangular object with black numbers

Description automatically generated

Now, the Date function, for the year in D3, and a sequence of 12 months, returns the FIRST DATE of every month, as below:

A screenshot of a computer

Description automatically generated

Check our dedicated, step-by-step tutorial video on calculating the starting dates of every month in a year using Excel:

Leave a Reply

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