Imagine having a spreadsheet with a start date and an end date, and you want to fill in all the dates between them in a column or row. In Google Sheets, you can easily achieve this by using an Array Formula.
Auto-Populate Dates Between Two Given Dates Vertically in Google Sheets
There are two options you can choose from to auto-populate dates between two given dates in Google Sheets:
1. ROW and INDIRECT Formula
The first formula makes use of the ROW and INDIRECT functions. Here is the formula:
=ArrayFormula(TO_DATE(row(indirect("A"&A2):indirect("A"&B2))))
To use this formula, simply replace A2 with your start date and B2 with your end date.
The logic behind this formula is similar to auto-populating sequential numbers in a column. Using the ROW function in an Array, you can generate a list of numbers. In this case, by using the INDIRECT function, you can obtain the values of the start and end dates. Combining these functions with the TO_DATE function allows you to convert the values into dates.
2. DAYS/DATEDIF and SEQUENCE
The second formula utilizes the DAYS/DATEDIF and SEQUENCE functions. Here is the formula:
=sequence(days(B2,A2)+1,1,A2)
This formula returns date values in a number format. To convert them into date format, follow these steps:
- Select the result range.
- Go to the menu Format > Number > Date.
Similarly to the previous formula, you can use the DATEDIF function in place of DAYS to calculate the number of days between two dates.
Auto-Populate Dates Between Two Given Dates Horizontally in Google Sheets
If you want to populate the dates horizontally, you can use the TRANSPOSE function in the first formula like this:
=ArrayFormula(TRANSPOSE(TO_DATE(row(indirect("A"&A2):indirect("A"&B2)))))
Alternatively, for the second formula, you can slightly modify the elements:
=sequence(1,datedif(A2,B2,"D")+1,A2)
Advanced Use of Expanding Dates in Google Sheets
Expanding dates between two given dates in Google Sheets can also be beneficial when using Vlookup. In an unsorted date column, Vlookup can only search for dates that are physically available in the cells. By expanding the start and end dates, you can replicate the corresponding column values and use them in Vlookup.
And that’s it! You now know how to auto-populate dates between two given dates in Google Sheets. Make the most of this feature to enhance your spreadsheets. Enjoy!
Check out Crawlan.com to explore more Google Sheets tips and tricks.
