nightwolfdevHave 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
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)
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))
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)))
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))))
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)))
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")
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!
