To make your life easier when working with dates in Google Sheets, there are simple methods you can use to automatically fill sequential dates. With just a formula or the fill handle, you can save time and effort.
The Fill Handle Method
The fill handle is a powerful tool in Google Sheets that allows you to quickly fill cells with sequential values. Here’s how it works:
- Enter a date in one cell, let’s say A1, and another date in the adjacent cell, A2.
- Select both cells and double click on the fill handle, the small square at the bottom right corner of the selected cells.
Voilà! The sequential dates will be automatically filled in the cells below, up to the last row with a value in the next column.
However, this method has a limitation. If you add more values in the next column, you will need to repeat the process.
The Formula Method
To overcome the limitation of the fill handle method, you can use a formula to automatically fill sequential dates. Here’s how you can do it:
- In cell A1, enter the following formula:
=SEQUENCE(COUNTA(B1:B), 1, DATE(2019, 11, 1))
This formula will generate a sequence of dates based on the number of values in column B. Before entering the formula, make sure to format column A as dates.
The SEQUENCE function is the key component of this formula. It creates a grid of sequential dates based on the number of rows specified by the COUNTA function, which counts the values in column B.
Auto-Fill Sequential Dates Excluding Hidden/Filtered Out Rows
If you want to auto-fill sequential dates only in visible rows, you can use a helper column along with the SUBTOTAL function. Here’s how:
- In column C, enter the following formula in cell C1 and copy it down to the last row:
=SUBTOTAL(103, B1)
The SUBTOTAL function with the argument 103 works as a COUNTA equivalent, but it excludes hidden or filtered-out rows. This will help us determine the number of visible rows with values in column B.
- In cell A1, use the following formula:
=SEQUENCE(COUNTIF(C1:C, ">0"), 1, DATE(2019, 11, 1), 2)
This formula will auto-fill sequential dates only in the visible rows, excluding any hidden or filtered-out rows. Again, don’t forget to format column A as dates.
By following these simple methods, you can save time and easily manage sequential dates in Google Sheets.
Enjoy using this delightful feature in Google Sheets! And for more tips and tricks, don’t forget to visit Crawlan.com.
