Populate and Filter Dates Between Start/End Dates in Google Sheets

nightwolfdev - May 17 '22 - - Dev Community

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

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 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)
Enter fullscreen mode Exit fullscreen mode

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 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))
Enter fullscreen mode Exit fullscreen mode

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 function can help with that. Update the formula in cell C2 with the following:

=ARRAYFORMULA(TO_DATE(SEQUENCE(B2-A2+1, 1, A2)))
Enter fullscreen mode Exit fullscreen mode

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 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))))
Enter fullscreen mode Exit fullscreen mode

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 function to filter the All Dates range based on the Ignored Dates range. The NOT and COUNTIF 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)))
Enter fullscreen mode Exit fullscreen mode

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 function. In cell F2, enter the following formula:

=QUERY(E2:E, "SELECT E WHERE dayOfWeek(E) = 6")
Enter fullscreen mode Exit fullscreen mode

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 and follow us on Twitter!

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .