To expand dates and assign values in Google Sheets is **useful if you have a schedule, and you want to assign a specific value at a given date which is between two dates of the schedule.**

##### Table of Contents

We will combine two functions and wrap them in a `QUERY`

function to accomplish this. We can use the `SEQUENCE`

function to expand dates and the `VLOOKUP`

function to assign values.

Let’s define the task and the expected output first:

- We have
**ranges of dates**in our data set, and**each range has a value**that we would like to**assign to an expanded list**of each date of these ranges.

We are referring to days as dates here. Each day in the ranges should appear in the expanded list, and their assigned value should be put next to these days.

Let’s visualize our expected output.

Here we have an input table with the date ranges (in cells **E2:F5**) and cities assigned to these dates (in cells **G2:G5**). The output we will create can be seen in cells **A1:B21**. The task is to have a separate cell for each day in the defined ranges and to put the assigned city names next to the cells of the days.

For example, a frequent traveller might want to have such a list with his travelling days and the destinations.

**⚠️ Ground Rules Before Starting to Work with This Example**

- Each date range has an assigned value, none of them is blank.
- The date ranges all have a start and end date.
- The date ranges don’t overlap, so each day is included in only one date range. (From our example: the traveller will not travel to more than one city in one day)
- But we allow non-connecting date ranges, so there can be days missing in between the date ranges that should not be included in the final list. (From our example: the days when the traveller stays at home)

## How to Expand Dates Using SEQUENCE Step by Step in Google Sheets

First, we want to expand the dates of our ranges. In other words, to auto-populate the dates. We are using the `SEQUENCE`

function to do this.

The syntax of the `SEQUENCE`

function is the following:

=SEQUENCE (rows, columns, start, step)

Let’s dissect this thing and learn about each of these terms:

the equal sign is just how we begin any function in Google Sheets.`=`

this is our function. We will have to add the corresponding value(s) into it for it to work.`SEQUENCE`

is a required field that represents the number of rows to return.`rows`

is an optional field that represents the number of columns to return. If you omit using it, the returned array will have one column.`columns`

is an optional field that represents the number to start the sequence at. If you ignore using it, the sequence will start at 1.`start`

is an optional field that represents the amount to increase (or decrease) each number in the sequence. By default, it will increase the sequence by 1.`step`

The number of rows is exactly what we would like to create an expression for. We need to calculate how many days are in between the date ranges of the example.

### Step 1: Find the Smallest and Largest Values of the Range

The first step to calculate this is to find the earliest and latest date in the range E2:F5. We can use the simplest `MIN`

and `MAX`

functions to get these dates and find the smallest and the largest dates of the ranges.

To get the earliest day of all the ranges, we will write:

=MIN(E2:F5)

And to get the latest day we will write the same, but with the `MAX`

function:

=MAX(E2:F5)

### Step 2: Expand the Dates with the SEQUENCE function

Next, we will put these expressions into the `SEQUENCE`

function.

We calculate the number of days between the latest (maximum) and earliest (minimum) dates. And since we want to include these bordering dates as well, we have to add 1 to this calculation.

We also want to define the starting value in the expanded list and write it as the third argument of the above-described `SEQUENCE`

syntax. It should be the first day of the earliest range so that the start argument will be the smallest date in the data set.

So we defined the and the

`rows`

**arguments. We are good with the default values for the other two arguments. However, as you can see below, we need to explicitly write the second argument (**

`start`

**= 1) to be able to define the third argument.**

`column`

The whole formula will look like this:

=SEQUENCE(days(MAX(E2:F5), MIN(E2:F5))+1,1, MIN(E2:F5))

When you enter this formula in the cell A2, you will get a column of numbers instead of dates, but don’t worry, it is fine. To format the values to proper date format, you have to go to the menu **Format > Number** and then click on ‘**Date**’.

Great, now we have all the days between the minimum and maximum dates of our date ranges.

The problem is that we have some extra days here that are not included in any of our start and end date ranges. For example, none of the original ranges has the days 11/4/2020 or 11/15/2020. We have to filter out the unwanted dates, but we will get back to this issue later.

### Step 3: Define the Start and End Dates of Each Value

The next step is to create a helper table to later assign the values to the days.

We copy each start date to a separate column and also the city names to the next column. After each start date in the helper table, we will insert a new row that has the respective date range’s end date + 1 day. Next to these dates, we will not put any city names but leave those cells blank.

This way, we guarantee that we defined where each date range ends and starting from which day it should not be assigned the city of the previous date range.

### How to Assign Values Using VLOOKUP in Google Sheets

We are using the powerful ** VLOOKUP** function of Google Sheets to assign values to the expanded dates.

**means vertically lookup for some value (search key) in our data set.**

`VLOOKUP `

The syntax of the ** VLOOKUP**function is:

=VLOOKUP(search_key, range, index, is_sorted)

Let’s dissect this as well and understand what each of these terms means:

the equal sign is just how we start any function in Google Sheets.`=`

this is our function. We will have to add the corresponding value(s) into it for it to work.`VLOOKUP`

are the values we want to lookup.`search_key(s)`

is where the lookup value is located.`range`

describes which column of the range are we using as values.`index`

is an optional argument. It’s 1 (`is_sorted`

*true*) by default meaning an approximate match (the nearest match is returned). If you want an exact match, you can set it*false*.

### Step 1: Use the Expanded List of Dates in a VLOOKUP function

Now let’s see how we should use this function in our example.

- The
will be the expanded list of dates because we want to lookup for these values.`search_key`

- The
should describe which value is assigned to which dates, so we should use our helper table here to define the start and end date for each city.`range`

- Th
will be 2, since we want to use the cities as return values and they are in the second column of the`index`

.`range`

- We don’t need the
argument here, because it makes no difference with text values.`is_sorted`

The ** VLOOKUP**function to assign values is:

=VLOOKUP(A2:A,E9:F,2,1)

If we write this is the first cell of the column of the assigned values (B2 in the example), it works because we get London as a result.

### Step 2: Use ArrayFormula to Repeat the Task on the Whole Data Set

We could repeat the previous expression in each cell of column B to assign the cities, but there is a simpler and more elegant solution.

We are using an to execute the function not only in one cell but in the whole array of dates at once.

`ArrayFormula`

=ArrayFormula(VLOOKUP(A2:A,E9:F,2,1))

This way, we don’t have to write the same expression in every cell of column B, but only in the first cell, and it will be performed on the whole array of dates.

The result can be seen in this picture:

## How to Do All This in One Formula and Filter Out Unwanted Rows Using QUERY

We might want to combine the ** SEQUENCE** and

**functions in one single formula to automate the whole table even more.**

`VLOOKUP`

Also, we still don’t have our expected result presented at the beginning, because there are extra rows in it.

### Step 1: Use the Previous Two Functions in One Array

Let’s use our previous two functions together in an array. We do this by putting two expressions into** {}** brackets, separated by a comma. First, we insert the ** SEQUENCE** function and then the

**function in the**

`VLOOKUP`

**.**

`ArrayFormula`

={SEQUENCE(days(MAX(E2:F5), MIN(E2:F5))+1,1, MIN(E2:F5)),ArrayFormula(VLOOKUP(A2:A,E9:F,2,1))}

### Step 2: Replace the Expanded Dates Cells with Its SEQUENCE Formula

The ** VLOOKUP** formula is using the results of the first

**formula as its search_key, so we can also replace the first argument of the**

`SEQUENCE`

**function with the**

`VLOOKUP`

**formula.**

`SEQUENCE`

={SEQUENCE(days(MAX(E2:F5), MIN(E2:F5))+1,1, MIN(E2:F5)), ArrayFormula(VLOOKUP(SEQUENCE(days(MAX(E2:F5), MIN(E2:F5))+1,1, MIN(E2:F5)),E9:F,2,1))}

### Step 3: Remove Empty Values Using QUERY

Now is the moment to filter out the rows without an assigned value. As you can see, there are many rows without an assigned city name. The solution to this is a bit more complicated, as it involves writing a ** QUERY** function.

We will use a query to remove the rows without an assigned value (without a city) in the second column.

Although this function is a bit more advanced, the syntax of the ** QUERY** function is very similar to other functions:

=QUERY(data, query, headers)

Let’s understand what each of these terms means:

the equal sign is just how we start any function in Google Sheets.`=`

this is our function. We will have to add the corresponding value(s) into it for it to work.`QUERY`

is the range of cells to perform the query on.`data`

is the query to perform, written in the query language of Google Sheets.`query`

are optional, and it means the number of header rows at the top of data.`headers`

The is all of the previous result set we just calculated above. We will put our result cell range here.

`data`

The part should be written in an SQL-like search query expression. We want to keep the rows where the assigned value (the second column) exist, in other words, where it is NOT blank. The following expression will do this:

`query`

**“Select * where Col2<>”**.

Therefore, the whole ** QUERY** function will look like this:

=QUERY( {our previous formula} , "Select * where Col2<>''", 0)

Let’s replace “our previous formula” with the actual formula:

=QUERY({SEQUENCE(days(MAX(E2:F5), MIN(E2:F5))+1,1, MIN(E2:F5)), ArrayFormula(VLOOKUP(SEQUENCE(days(MAX(E2:F5), MIN(E2:F5))+1,1, MIN(E2:F5)),E9:F,2,1))}, "Select * where Col2<>''", 0)

We just have to write this combined formula in cell A2 and hit **Enter**. As a result, we have a perfect list with the expanded dates and their assigned values.

That’s it! This is how you expand dates and assign values in Google Sheets using the SEQUENCE, VLOOKUP and QUERY functions. 👏🏆

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

You can now expand dates and assign values together with the **other numerous Google Sheets formulas** to create even useful powerful. 🙂

## 1 comment

Hi,

Great to see such lucid explanation of transforming data by expanding date range.

I have a requirement of generating dates from date range given in say Cell range A1:A and B1:B ( start date end date respectively) .each row in a google sheet has some action item for a date range.

I need to expand this data to show date wise action item. tried to improvise code by you using array formula but array formula did not work.

any pointers would be greatly appreciated