Populate and Filter Dates Between Start/End Dates in Google Sheets
Tags: googlesheets
Published: 2022-05-17 17:10:00

Have you ever needed to populate a column with all the dates between a start and end date? What if you needed to ignore certain dates? What if you only wanted Friday dates? Let’s learn how in Google Sheets!

## Spreadsheet Setup

![Populate and Filter Dates Between Start/End Dates in Google Sheets](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7s6pje193adtikyuyun0.png)

Create the following columns:

- Start Date
- End Date
- All Dates
- Ignored Dates
- Filtered Dates
- Fridays

## Sequential Numbers

Did you know that dates are actually represented as numbers internally? We can create a sequence of numbers that represent dates using the [SEQUENCE](https://support.google.com/docs/answer/9368244) function. You provide it the number of rows, columns, and a starting value.

The number of rows will be the End Date (**B2**) minus the Start Date (**A2**) plus 1. If we don’t add 1, the End Date won’t be included. The number of columns is 1 because we only want 1 column. The start value is the Start Date (**A2**).

Add the following formula in cell **C2**:

```
=SEQUENCE(B2-A2+1, 1, A2)
```

You now have a list of numbers representing all the dates between the Start and End dates (including the Start and End dates). However, we want actual dates, not numbers representing dates.

## Convert Number to Date

To convert a number to a date, we can use the [TO_DATE](https://support.google.com/docs/answer/3094239) function. You provide it a number and it converts it to a date. Update the formula in cell **C2** with the following:

```
=TO_DATE(SEQUENCE(B2-A2+1, 1, A2))
```

What happened? Now there’s only one date appearing? The TO_DATE formula only worked on the first value in the sequence of numbers. We want to do that to all the numbers in the sequence. The [ARRAYFORMULA](https://support.google.com/docs/answer/3093275) function can help with that. Update the formula in cell C2 with the following:

```
=ARRAYFORMULA(TO_DATE(SEQUENCE(B2-A2+1, 1, A2)))
```

Awesome! You now have all the dates between the start and end date! Change the start/end dates and see the list of dates also change!

_NOTE: If you are still seeing numbers instead of dates, select column C and navigate to Format > Number > Date._

## Avoid Reference Errors

Formulas work great when the cells they reference have values. What if you haven’t defined a start or end date yet? That’s when you’ll see errors. We can clean that up easily.

In cell **C2**, update the formula by wrapping it in the [IFERROR](https://support.google.com/docs/answer/3093304) function. It will return the first argument if it’s not an error value, otherwise it will return whatever you define as the second argument, or blank if a second argument is not provided.

```
=IFERROR(ARRAYFORMULA(TO_DATE(SEQUENCE(B2-A2+1, 1, A2))))
```

Remove either the Start Date or End Date value and no error will appear in the other cells that reference those values.

## Filter Dates

Define a few dates under the **Ignored Dates** column (D). Under the **Filtered Dates** column (E), we want to list all the dates from the **All Dates** column (C), excluding those defined under the **Ignored Dates** column (D).

Let’s use the [FILTER](https://support.google.com/docs/answer/3093197) function to filter the All Dates range based on the Ignored Dates range. The [NOT](https://support.google.com/docs/answer/3093305) and [COUNTIF](https://support.google.com/docs/answer/3093480) functions will help provide a true/false condition when comparing the ranges. In cell **E2**, enter the following formula:

```
=FILTER(C2:C, NOT(COUNTIF(D2:D, C2:C)))
```

## Friday Dates

What if you wanted dates specific to a day of the week? You can query data using SQL like syntax with the [QUERY](https://support.google.com/docs/answer/3093343) function. In cell **F2**, enter the following formula:

```
=QUERY(E2:E, "SELECT E WHERE dayOfWeek(E) = 6")
```

The **Filtered Dates** column (E) is being used as the source of data. We select the E column and grab any date where the day of week is equal to 6 (Sunday is 1, Monday is 2, etc).

---
**Visit our website at [https://nightwolf.dev](https://nightwolf.dev) and follow us on [Twitter](https://twitter.com/nightwolfdev)!**