In this post from our Data to Decisions series, we focus on generating all the dates of a given year in Excel, a foundational skill for anyone involved in planning, scheduling, or tracking events. With Excel’s SEQUENCE, and DATE functions, creating a series of dates becomes a task of mere seconds, read along.
Let us consider an input year in cell G2 as shown:
To generate sequential dates in a year, first, let’s calculate the number of days in a year (in G3). We’ll use the DATE function to calculate this.
The DATE function, takes in arguments of a year, month and day and returns a date,
So, our formula in cell G3 will be:
=DATE(G2+1, 1, 1) - DATE(G2, 1, 1)Essentially, this is calculating the number of days between the start of the next year to the first day of the given year.
This calculation is crucial since the number of days in a year is different for leap years, using the above function helps us make the function dynamic, to handle any year
Now, we have the number of days we need, if we calculate the starting date then, we can use the SEQUENCE function to generate all the dates of a year.
To get the starting date, we’ll use the DATE function.
=DATE(G2,1,1) This gives us the first date of the year as given in cell G2
Let us look at a crucial Excel function we are using here, the SEQUENCE function
This function takes as input, the rows to create, columns to create (optional), starting point (optional), and the steps to increment each time (also optional). Let us look at this as a syntax:
Incorporating our DATE formula inside a SEQUENCE function, our final formula will be:
=SEQUENCE(G3, 1, DATE(G2,1,1), 1)Here, the sequence function generates a series of dates (as in cell G3) in a single column starting from the first date of the year arrived by using the DATE function.
This returns all the dates of the given year from cell G6 onwards
We have a dedicated video on generating continuous series of dates under different scenarios, do check it out.
