To auto-populate dates between two given dates in Google Sheets is **useful if you have a start date and an end date and you want to fill all the dates between these two dates in a column.**

##### Table of Contents

Auto-populating means filling a column automatically with the values between two given values. You can auto-populate both numbers and dates to make your work faster and easier.

Let’s take an example.

Say we have a start date and an end date. We want to expand the dates between these two given dates in our spreadsheet and see the days one by one in a column.

**So how do we do that? **

Simple. We can use the SEQUENCE function and some additional functions, ARRAYFORMULA and TO_DATE, to do this automatically.

In this guide, I’ll show you how you can do this with one single formula.

Let’s dive right in. 🤜

## The Anatomy of the Auto-Populating Functions

We’re showing the combination of three different functions to auto-populate dates between two given dates in Google Sheets.

### The SEQUENCE Function

The `SEQUENCE`

function is used to return an array of sequential numbers.

The syntax (the way we write) the `SEQUENCE`

function is the following:

=SEQUENCE (rows, columns, start, step)

The variables of the function are:

`rows`

means the number of rows we want in the sequence,`columns`

means the number of columns where we want to expand the sequence,`start`

is the first value of the sequence,`step`

indicates the difference between two values of the sequence. Its default value is 1.

To understand how to use exactly the `SEQUENCE`

function in Google Sheets, check our article that explains it in details.

Since Google Sheets treats every date as integer numbers in the background, we can use the `SEQUENCE`

function with dates as well.

### The TO_DATE Function

The `TO_DATE`

function converts a given number to date.

Its syntax is as follows:

=TO_DATE(value)

It takes one cell with a number (as the `value`

variable) and converts it to date.

Although we could convert our numbers to dates by formatting them in the menu (**Format > Number > Date**), using this function instead saves a lot of time when there are many rows.

### The ARRAYFORMULA Function

The `ARRAYFORMULA`

function in Google Sheets applies a formula to an entire column in Google Sheets.

It’s used when we have a function that normally works on one cell, but we want to use it on a whole range of cells. For example, the `TO_DATE`

function works with one cell, but we want to use it on a whole column.

The syntax of the `ARRAYFORMULA`

function is:

=ARRAYFORMULA(array_formula)

The `array_formula`

variable is the function that we want to wrap in an `ARRAYFORMULA`

. We can use any function in the `ARRAYFORMULA`

function that can be applied to a whole range of cells.

Read more about the ARRAYFORMULA function in our article.

So, after having gained some insights into the anatomy of these functions, let’s use them in a real-life example.

### A Real Example to Auto-Populate Dates Between Two Given Dates

Have a look at the example below to see how to auto-populate dates between two given dates in Google Sheets.

The picture below shows the expected result. It contains the start date and the end date, together with the auto-populated list of dates.

The function we used here is as follows:

=ARRAYFORMULA(TO_DATE(SEQUENCE(B1- A1 +1,1,A1)))

Let’s break this down to understand what each of the parts means:

- First, look at the most inner function of this formula. We used the
`SEQUENCE`

function to create the array of sequential numbers. Remember that every date is a number in the background, that’s what we used to calculate the`rows`

variable. - The
`rows`

variable of the`SEQUENCE`

function is the number of rows we want in our sequence. Thus, it’s the difference between the start and end date, plus one because we want to include the end date itself too. We used the cell references of the start and end dates, subtracted them and added 1 to the result. So, the rows variable is**B1 – A1 + 1**. - Then, the
`columns`

variable is 1 because we only need one column. - The
`start`

variable is the start date which is in cell**A1**. - Then, we used the
`TO_DATE`

function to convert the numbers to dates. - Finally, we wrapped the whole function in the
`ARRAYFORMULA`

function to ensure that the`TO_DATE`

function is applied to every cell of the result of the`SEQUENCE`

function.

As a result, we got a list with auto-populated dates between the start date and the end date.

It’s simple, right?

Feel free to make a copy of the spreadsheet using the link I have attached below and try it for yourself:

## How to Auto-Populate Dates Between Two Given Dates in Google Sheets

Let’s see how can you auto-populate dates between two given dates in Google Sheets step-by-step.

- To start, select the cell where you want to have the first date of the auto-populated range. Make sure to have sufficient empty area below this cell, because the function will expand down the entire column.

- Then, enter the equal sign ‘
**=**’ to begin the formula and then followed by the name of the`SEQUENCE`

function. For this guide, I will be typing it in cell**D2**.

- After the opening bracket ‘
**(**‘ of the`SEQUENCE`

function, add the first variable,`rows`

. To define how many rows you want in your sequence, subtract the start date from the end date. For this guide, I will be selecting the cells**B2**and**A2**to calculate this difference.

- Then, add 1 to this number if you want to include the end date as well. Otherwise, you would only auto-populate the dates until the day before the end date. Put a comma after each variable of the function.

- After that, add the
`columns`

variable which is 1 in this example.

- To finish the
`SEQUENCE`

function, add the`start`

variable that is the first number (date) of your sequence. In the example, I will be selecting the cell**A2**that has my start date.

- Great! This formula would already return a sequence of numbers that can be formatted as dates. However, I’m showing a way to include the formatting in the formula itself. Close the brackets of the
`SEQUENCE`

function and move your cursor after the equal sign at the beginning. After that, type the name of the`TO_DATE`

function here.

- There is one more thing left. Converting the single-cell result of the
`TO_DATE`

function into an array. Therefore, close the brackets of the`TO_DATE`

function as well and go back to the equal sign again. Wrap the whole formula in an`ARRAYFORMULA`

function.

- Finally, hit your
**Enter**key to see the whole sequence of auto-populated dates.

That’s it! This is how you auto-populate dates between two given dates in Google Sheets using the SEQUENCE, TO_DATE and ARRAYFORMULA functions. 👏🏆

Now you can use it together with the **other numerous Google Sheets formulas** to create even more effective formulas. 🙂

## 3 comments

Ha! Got it working! Great little formula. Thanks!

Could the formula be expanded to include other ranges?

Say for example columns A & B had multiple ranges (A3 to B3, A4 to B4, etc), could we list all sequences in sorted order in column D?

Hey I also have the same question as above

Could the formula be expanded to include other ranges?

Say for example columns A & B had multiple ranges (A3 to B3, A4 to B4, etc), could we list all sequences in sorted order in column D?