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.
=DATE(D3, SEQUENCE(12,1,1,1), 1)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:
Now, the Date function, for the year in D3, and a sequence of 12 months, returns the FIRST DATE of every month, as below:
Check our dedicated, step-by-step tutorial video on calculating the starting dates of every month in a year using Excel:
Looking to calculate the end dates of months? Check our blog.
