Indzara

How to Generate all the Dates of a Year in Excel?

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:

Generate all the Dates of a Year  user input

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,

Generate all the Dates of a Year DATE function

So, our formula in cell G3 will be:

Essentially, this is calculating the number of days between the start of the next year to the first day of the given year.

Generate all the Dates of a Year sequence of dates

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.  

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:

Generate all the Dates of a Year  SEQUENCE  function

Incorporating our DATE formula inside a SEQUENCE function, our final formula will be:

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

Generate all the Dates of a Year

We have a dedicated video on generating continuous series of dates under different scenarios, do check it out.

Leave a Reply

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