Some of you may wonder why one would want to increment duplicate dates by month or day in Google Sheets. Let me explain.
In a previous article, I shared a formula to help my readers create duplicate entries in Google Sheets. This formula has proven to be useful in automating future entries based on a specific column.
However, when using the formula, the dates do not increment by day or month as desired. Instead, the formula duplicates the existing dates. Let me illustrate this with an example.
Assume we have the following monthly subscription details for two services:
| Date of Renewal | Description | Amount | No. of Times in a Year |
|---|---|---|---|
| January 1, 2022 | Service A | $10 | 12 |
| January 1, 2022 | Service B | $15 | 12 |
Using the formula, we can automate the duplication of these entries based on the frequency specified in the fourth column. However, the dates remain the same.
To address this, I will explain how to increment duplicate dates by either day or month in Google Sheets.
The Logic Behind
To increment a date by month, we can utilize the EDATE function in Google Sheets. The function works as follows:
=edate("01/01/2022",1)
The above formula increments the date January 1, 2022, by one month, resulting in February 1, 2022.
For incrementing dates by day, we don’t need the EDATE function. Instead, we can simply add the numbers 1, 2, 3, and so on, to the dates to increment them accordingly.
Formula to Increment Duplicate Dates by Month
Let’s start with the non-array formulas to understand the logic better, and then we can automate the process using Array Formulas.
To increment the duplicate dates in column F by month, follow these steps:
- In column I, enter the sequential numbers from 0 to 11. This represents the number of months to increment in each row.
- In cell J2, insert the formula
=edate(F2,I2)and drag it down.
By following these steps, we can increment duplicate dates by month in Google Sheets.
How to Automate It?
To automate the process, we need to populate the sequential numbers dynamically and make the formula an array formula. Here are the steps to achieve this:
- Use the following formula in cell I2 to get the group-wise serial numbers based on the data:
=ArrayFormula(if(len(F2:F),row(F2:F)-match(F2:F&G2:G,F2:F&G2:G,0)-1,))
-
Empty the cell range I2:I and then copy-paste the formula into I2.
-
Now, make the EDATE formula in cell J2 an array formula. Empty the range J2:J and use the following formula in J2:
=ArrayFormula(edate(F2:F,I2:I))
- To combine both formulas and avoid using extra columns, replace the reference I2:I in the EDATE formula with the E2 formula itself:
=ArrayFormula(edate(F2:F,ArrayFormula(COUNTIFS(F2:F&G2:G,F2:F&G2:G,ROW(F2:F),"<="&ROW(F2:F)-1))))
When inserting this formula, please remember to limit the formula expansion to non-blank rows and add a title. Insert the formula in the header row of the table.
Here is the modified formula to increment duplicate dates by month in Google Sheets. Empty columns I and J and enter the formula in I1:
={"Date";ArrayFormula(if(len(F2:F),(F2:F+COUNTIFS(F2:F&G2:G,F2:F&G2:G,ROW(F2:F),"<="&ROW(F2:F)-1)),))}Make sure to format the dates in column I2:I as dates by selecting the range and applying Format > Number > Date.
Formula to Increment Duplicate Dates by Day
In this case, we only need to add 0 to the first occurrence of each date, 1 to the second occurrence, and so on. We don’t need to use the EDATE function here.
To increment duplicate dates by day, follow these steps:
- Use the earlier running count formula in cell I2 to get a sequence (group-wise). Format I2:I as numbers if needed.
- Instead of using the EDATE formula, use the following formula in cell J2:
=ArrayFormula(F2:F+I2:I)
By following these steps, you can increment duplicate dates by day.
To combine both formulas and place them in I1 (header row), follow these steps:
- Replace the reference I2:I25 in the above formula with the I2 formula itself:
=ArrayFormula(F2:F+ArrayFormula(COUNTIFS(F2:F&G2:G,F2:F&G2:G,ROW(F2:F),"<="&ROW(F2:F)-1)))
- Delete the existing formula in cell I2, modify the above formula as shown below, and cut and paste it into cell I1:
={"Date";ArrayFormula(if(len(F2:F),(F2:F+COUNTIFS(F2:F&G2:G,F2:F&G2:G,ROW(F2:F),"<="&ROW(F2:F)-1)),))}Format column I2:I as dates. That’s all!
I hope you found this explanation helpful in incrementing duplicate dates by day or month in Google Sheets. If you have any questions, feel free to ask.
Thanks for reading, and enjoy!
Sample_Sheet_3521
