Some of you may ask why one should want to increment duplicate dates by month or day in Google Sheets. So let me start with that.

I have shared one formula in the past to help my readers to create duplicate entries in Google Sheets. That helps in the following way.

Assume I have the following entries.

The above is my monthly subscription details of two services for the first month of the year. There are two service providers.

If they charge the amount every month on the same date, I would have 12 entries each for the whole year.

With the help of my earlier formula, we can automate future entries. I mean, we can make duplicates of the above entries based on the 4th column.

While doing so, the dates won’t increment by day or month. Instead, the formula would make duplicates of dates. Didn’t get it?

In the below screenshot, the above first month entries are in the cell range A2:D3. The F2 formula makes the duplicates of these entries (two records) in F2:H25.

=ArrayFormula(vlookup(transpose(split(query(rept(row(A2:A)&" ",D2:D),,9^9)," ")),{row(A2:A),A2:C},{2,3,4},0))

Please read the formula explanation here – How to Insert Duplicate Rows in Google Sheets.

I want to increment the duplicate dates in column F by months. How to achieve that?

In this post, I am just explaining how to increment duplicate dates by days or months.

The Logic Behind

We can increment a date by month using the EDATE function.

It’s like;

=edate("01/01/2021",1)

The above EDATE formula will increment the date 01/01/2021 by one month and will return the date 01/02/2021.

Note:- The above dates are in dd-mm-yy format, which is the default date format in my Sheet. If yours’ is in mm-dd-yy, then follow that in the formula.

The last parameter in the formula defines the number of months to add to the given date. In the above, it is 1. Let’s change it to 2.

=edate("01/01/2021",2)

You will get 01/03/2021. That’s the logic I am going to use to increment duplicate dates by month in Google Sheets.

Then what about incrementing duplicated dates by date?

Here the EDATE is not required. We can straightaway add the numbers 1, 2, 3, etc., to the dates to increment by day.

Formula to Increment Duplicate Dates by Month

First of all, we will use non-array formulas. That will give you a clear idea of the logic above. Then we can automate the same using Array Formulas.

Let’s increment the duplicate dates in column F by month in Google Sheets.

Things to Do

1. There are twelve entries each. So in column I, enter the sequential numbers 0 to 11 twice (the number of months to increment in each row).
2. In cell J2, insert the formula =edate(F2,I2) and drag it down.

This way, we can increment duplicate dates by month in Google Sheets.

How to Automate It?

Automate here means populating the sequential numbers in column I dynamically (based on the data) and making the E2 formula an array one. Then combine both.

There are three steps involved, and here are them.

To get the numbers based on groups (we have two sets of data means two groups), we can use my below group-wise serial number formula in cell I2.

=ArrayFormula(if(len(F2:F),row(F2:F)-match(F2:F&G2:G,F2:F&G2:G,0)-1,))

or the below Countif Running Count formula.

=ArrayFormula(COUNTIFS(F2:F&G2:G,F2:F&G2:G,ROW(F2:F),"<="&ROW(F2:F)-1))

I prefer the just above one.

Empty the cell range I2:I and then copy-paste the above formula in I2.

The next step is to make the EDATE in cell J2 an array one. To do that, empty J2:J and use the below EDATE formula in J2.

=ArrayFormula(edate(F2:F,I2:I))

The above EDATE itself will increment the duplicate dates by month. But we must combine both the formulas to avoid using extra columns.

It’s simple. Replace the reference I2:I in the EDATE formula with the E2 formula.

=ArrayFormula(edate(F2:F,ArrayFormula(COUNTIFS(F2:F&G2:G,F2:F&G2:G,ROW(F2:F),"<="&ROW(F2:F)-1))))

Wait a moment, please. We must modify the formula a little bit.

We should limit the formula expansion to the non-blank rows. Further, let’s add a title. When adding a title, we must insert the formula in the header row of the table.

Here is that fine-tuned formula to increment duplicate dates by month in Google Sheets. Empty the columns I and J and key it 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)),))}

Probably the dates in I2:I will be in number format. Change that to date by selecting the range then applying Format > Number > Date.

Formula to Increment Duplicate Dates by Day

Here, we are only required to add 0 to the first occurrence, 1 to the second occurrence of the date, etc. There is no role of EDATE here.

Do you want to learn this also step by step? If Yes, follow the below steps.

We can use our earlier running count formula in I2 to get sequence (group-wise). You may be required to format I2:I to number.

Then in cell J2, instead of the EDATE, we can use the below formula.

=ArrayFormula(F2:F25+I2:I25)

The above Array formula increments duplicate dates by day.

Let’s combine both the formulas and place them in I1 (header row) as earlier. Here is how.

First, in the above formula, replace the reference I2:I25 with the I2 formula itself. Here is that one.

=ArrayFormula(F2:F25+ArrayFormula(COUNTIFS(F2:F&G2:G,F2:F&G2:G,ROW(F2:F),"<="&ROW(F2:F)-1)))

Then, go to cell I2 and delete the existing formula. Fine-tune the above formula as below and cut and paste it in 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 I2:I to date. That’s all.

I hope you could understand how to increment duplicate dates by day or month in Google Sheets.

Thanks for the stay. Enjoy!

Sample_Sheet_3521