CODEWITHSUNDEEP
javascriptdategoogle-apps-script
Daniel2077
Daniel2077

Reputation: 11

How to subtract 5 days from a defined date - Google App Script

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

Upvotes: 1

Views: 1488

Answers (7)

Prasenjeet Symon
Prasenjeet Symon

Reputation: 192

You can subtract 5 days from a defined date in Google App Script by using the Utilities.formatDate() method. Here's an example:

function subtractDays() {
  var date = new Date();
  var subtractDays = 5;

  // Subtract 5 days from the current date
  date.setDate(date.getDate() - subtractDays);

  // Format the new date
  var newDate = Utilities.formatDate(date, "UTC", "yyyy-MM-dd");

  Logger.log(newDate);
}

In this example, we first create a Date object to represent the current date. Then, we subtract 5 days from the current date by using the setDate() method. Finally, we format the new date using the Utilities.formatDate() method and log it to the console using the Logger.log() method.

You can modify the subtractDays variable to subtract a different number of days from the date, or you can use a different date object to start with.

Upvotes: -1

pixelearth
pixelearth

Reputation: 14630

If what's coming from the sheet is a string, you will have to convert the date string into a date object.

The other thing is you have to work in milliseconds as @vanowm says:

606024*5 = 432000 * 1000 = 432000000

so skipping the sheet entirely:

x = new Date
> Fri May 27 2022 11:24:01 GMT-0400 (Eastern Daylight Time)

y = new Date(x - 432000000)
> Sun May 22 2022 11:24:01 GMT-0400 (Eastern Daylight Time)

Upvotes: 1

TheMaster
TheMaster

Reputation: 50462

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);

Upvotes: 1

Taurz
Taurz

Reputation: 390

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

Upvotes: 1

Cooper
Cooper

Reputation: 64072

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;
}

Upvotes: -1

Dmitry Kostyuk
Dmitry Kostyuk

Reputation: 1459

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));
};

Upvotes: 0

NEWAZA
NEWAZA

Reputation: 1630

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)))

}

Upvotes: 1

Related Questions