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
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:
rowsmeans the number of rows we want in the sequence,
columnsmeans the number of columns where we want to expand the sequence,
startis the first value of the sequence,
stepindicates 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
TO_DATE function converts a given number to date.
Its syntax is as follows:
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
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:
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.
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
SEQUENCEfunction 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
rowsvariable of the
SEQUENCEfunction 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
columnsvariable is 1 because we only need one column.
startvariable is the start date which is in cell A1.
- Then, we used the
TO_DATEfunction to convert the numbers to dates.
- Finally, we wrapped the whole function in the
ARRAYFORMULAfunction to ensure that the
TO_DATEfunction is applied to every cell of the result of the
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
SEQUENCEfunction. For this guide, I will be typing it in cell D2.
- After the opening bracket ‘(‘ of the
SEQUENCEfunction, 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
columnsvariable which is 1 in this example.
- To finish the
SEQUENCEfunction, add the
startvariable 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
SEQUENCEfunction and move your cursor after the equal sign at the beginning. After that, type the name of the
- There is one more thing left. Converting the single-cell result of the
TO_DATEfunction into an array. Therefore, close the brackets of the
TO_DATEfunction as well and go back to the equal sign again. Wrap the whole formula in an
- Finally, hit your Enter key to see the whole sequence of auto-populated dates.
Now you can use it together with the other numerous Google Sheets formulas to create even more effective formulas. 🙂