I’m about to let you in on a little Google Sheets secret that will save you time and make your life easier. Imagine having two dates in a spreadsheet – a start date and an end date – and wanting to populate all the dates between them in a column or row. Well, it’s possible with just a few simple steps!
Auto-Populate Dates Between Two Given Dates Vertically in Google Sheets
There are two combinations of formulas that you can use for this task:
1. ROW and INDIRECT Formula
Let’s start with the first formula. Simply use the following formula to get all the dates between your start and end dates vertically in Google Sheets:
=ArrayFormula(TO_DATE(row(indirect("A"&A2):indirect("A"&B2))))Here, A2 represents the start date, and B2 represents the end date. Make sure to change these cell references according to your sheet.
The logic behind this formula involves using the ROW function in combination with the INDIRECT function. The ROW function generates a sequence of numbers, while the INDIRECT function refers to specific cells based on those numbers. By combining these two functions, we can populate the dates between the start and end dates.
2. DAYS/DATEDIF and SEQUENCE
Alternatively, you can use the following formula:
=sequence(days(B2,A2)+1,1,A2)This formula calculates the number of days between the dates in cells B2 and A2 and returns the result in that many rows. The SEQUENCE function is used to generate the sequence of dates. To format the result as dates, select the range and go to Format > Number > Date.
You can also replace the DAYS function with the DATEDIF function if you prefer:
=sequence(datedif(A2,B2,"D")+1,1,A2)Both of these formulas are excellent options for auto-populating dates between two given dates in Google Sheets.
Auto-Populate Dates Between Two Given Dates Horizontally in Google Sheets
In certain situations, you may want to populate the dates horizontally, such as in the header of a Gantt chart bar area. Here’s how you can do it using the formulas mentioned earlier:
For the first formula, use the TRANSPOSE function to change the orientation of the auto-populated dates:
=ArrayFormula(TRANSPOSE(TO_DATE(row(indirect("A"&A2):indirect("A"&B2)))))For the second formula, you can either use the TRANSPOSE function or slightly modify the elements:
=sequence(1,datedif(A2,B2,"D")+1,A2)Both of these options will give you the dates populated horizontally.
Advanced Use of Expanding Dates in Google Sheets
Now that you know how to auto-populate dates between two given dates in Google Sheets, let me share an advanced tip with you. You can use this technique in combination with Vlookup to overcome one of its drawbacks.
In Google Sheets, when using Vlookup in an unsorted date column, the function can only lookup dates that are physically available in the cells. It cannot lookup dates between the provided start and end dates. However, by expanding the dates as we learned earlier, you can replicate the corresponding column values and use them in Vlookup.
To delve deeper into this concept, check out our advanced Vlookup tutorial exclusively on Crawlan.com.
So there you have it – a handy trick to auto-populate dates in Google Sheets. I hope you find this information useful and start saving time on your spreadsheet tasks. Enjoy!
