How do I loop adding the month to each date until the condition is met?

Explanation : I want to add 3 months to each date every time until the annual column (column D) will be greater than the today's date ( column E).

Example : If we took the first line of the column Annual " 01/02/2021" and we add at first 3 months the results will be 01/05/2021 then we add second time 3 months 01/08/2021 ..etc until it will be 01/05/2022 ( Greater than the today's date).

In the column "H" I've write the results I want, but whats I've got is in the column "F" by Red.

Here's my code :

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  for (var i = 2; i < 12; i  ) {
    var today_date = ss.getRange(i, 5).getValue();
    var today_date_format = Utilities.formatDate(new Date(today_date), "Europe/Paris", 'MMMM dd, yyyy 12:00:00 Z');
    var Date_2021 = ss.getRange(i, 4).getValue();
    var Date_2021_Format = Utilities.formatDate(new Date(Date_2021), "Europe/Paris", 'MMMM dd, yyyy 12:00:00 Z');
    var Date_2021_constructed = new Date(Date_2021_Format);
    var a3date = Date_2021_constructed.getMonth();
    Date_2021_constructed.setMonth((a3date   3) % 12);
    do {
      Date_2021_constructed = Date_2021_constructed.setMonth((a3date   3) % 12);
    } while (Date_2021_constructed > today_date_format);
    ss.getRange(i, 6).setValue(Date_2021_constructed)
  }
}

Here's the screen of my data :

enter image description here

CodePudding user response：

Thanks for the example and the image that helps a lot:

I'm going to suggest a quick fix and see if it works. It looks like you may be posting the value of the date in millisecond in column f. So try this:

ss.getRange(i, 6).setValue(new Date(Date_2021_constructed)).setNumberFormat("dd/MM/yyyy");

If this doesn't work I'll take a closer look and it would be helpful to have a table of values from your spreadsheet so that I can test.

If the above solution does not work then try this:

function myFunction() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet1');
  const vs = sh.getRange(2,1,sh.getLastRow() - 1,5).getValues();
  vs.forEach((r,i) => {
    let td = new Date(r[4]);
    let tdv = td.valueOf();
    let ad = new Date(r[3]);
    let m = r[2];
    let n = 1;
    do{
      var fd = new Date(ad.getFullYear(),ad.getMonth()   (n   * m), ad.getDate());
      var fdv = fd.valueOf();
    }while(fdv<tdv);
    sh.getRange(i   2,6).setValue(fd).setNumberFormat("dd/MM/yyyy");
  });
}