I'm trying to write a script to subtract 5 days from a defined date but seems not working, here's my code:

var End_Day = sheet.getRange(i   2, 20).getValue();
Logger.log(End_Day);
var End_day_2 = new Date();
End_day_2.setDate(End_Day.getDate()-5);
Logger.log(End_day_2);

and the result is not just - 5 days:

11:18:47 AM Info    Sat Jun 04 00:00:00 GMT 08:00 2022
11:18:47 AM Info    Fri Apr 29 11:18:47 GMT 08:00 2022

I am quite confused why the date move from Jun to Apr.

Thanks for having a look

CodePudding user response：

Try:

var End_Day = sheet.getRange(i   2, 20).getValue();
var End_day_2 = new Date(End_Day.getTime() - (5 * (1000 * 60 * 60 * 24)))
Logger.log(End_Day);
Logger.log(End_day_2);

Function:

const endDay = sheet.getRange(i   2, 20).getValue()
const endDay2 = DateFromDaysAgo(endDay, 5)

...

function DateFromDaysAgo(startDate, number) {

  if (typeof startDate === `string`) { startDate = new Date(startDate) }

  return new Date(startDate.getTime() - (number * (1000 * 60 * 60 * 24)))

}

CodePudding user response：

You should learn more about Date.prototype.setDate().It only changes the day of the month of a given Date instance.

As the code you posted, the day of the month of End_Day is 4, End_day_2.setDate(4 - 5) equals to End_day_2.setDate(-1) and the month of End_day_2 is April according to the console result, because there're 30 days in April, setDate(-1) means setDate(29), so you got Apr 29 at the end. That's how it goes.

One right way to do is substracting 5 days worth of milliseconds.

function addDays(date, days){
  const DAY_IN_MILLISECONDS = 24 * 60 * 60000;
  return new Date(date.getTime()   days * DAY_IN_MILLISECONDS);
}

console.log(addDays(new Date(), -5).toString()); // 5 days ago

CodePudding user response：

This will do the trick. Works with any date and can subtract any number of days

const subtractDays = (fromDate, numDays) => {
  if (!(fromDate instanceof Date)) throw 'The first argument must be a date';
  return new Date(new Date().setDate(fromDate.getDate() -  numDays));
};

CodePudding user response：

As other posts are already showing you the issue is that the first date you have is a string and not a date object. If you want to subtract dates you have to have date objects in both cases. You will have to convert the date string into a date object . I just wanted to put this into words instead of throwing a bunch of code at you.

CodePudding user response：

I am quite confused why the date move from Jun to Apr.

It's because you're setting date on today(End_day_2) and not on your predefined date(End_day).

Change

End_day_2.setDate(End_Day.getDate()-5);

to

End_day.setDate(End_Day.getDate()-5);
console.info(End_day);

CodePudding user response：

I think for Date manipulation you should use stable libraries like Moment.js

Here is the code sample to subtract 5 days from current date

moment().subtract(5, 'days');

To install moment.js in your project use npm i moment

You can also use dayjs

CodePudding user response：

Weekago

function weekago() {
  let dt = new Date();
  dt.setDate(dt.getDate()-7);
  Logger.log(dt);
  return dt;
}

Five days ago

function fiveago() {
  let dt = new Date();
  dt.setDate(dt.getDate()-5)
  Logger.log(dt);
  return dt;
}

Five days from a date in a spreadsheet cell

function fivefromadateinspreadsheet() {
  const v = SpreadsheetApp.getActiveSheet().getRange("A1").getValue();
  let dt = new Date(v);
  dt.setDate(dt.getDate()-5);//Note that does not return a date it return the numbrer of milliseconds
  Logger.log(dt);
  return dt;
}