1

I have a Google Sheet for a weekly meeting. I currently have Column A automatically filling the date of when I enter anything into the box in Column B. Since I typically put the date of the next meeting, not the date I add the note, I wondered if there was a script I could add that would take Date X and 'round up' to the next meeting date. Meetings are always on Wednesday.

I was looking around for something to do this but was only finding how to round up to the nearest minute, which is not as specific as I need.

For example, my last meeting was 9/27. I would want anything I enter in Column B after 9/27 to show a timestamp of 10/4, even if I enter it into the sheet on 10/2 or 10/3.

Currently running this (from internetgeek.org):

function onEdit(event)
{ 
  var timezone = "GMT-5";
  var timestamp_format = "MM/dd/yyyy";  
  var updateColName = "Item";
  var timeStampColName = "Date";
  var sheet = event.source.getSheetByName('Agenda'); 


  var actRng = event.source.getActiveRange();
  var editColumn = actRng.getColumn();
  var index = actRng.getRowIndex();
  var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();
  var dateCol = headers[0].indexOf(timeStampColName);
  var updateCol = headers[0].indexOf(updateColName); updateCol = updateCol+1;
  if (dateCol > -1 && index > 1 && editColumn == updateCol) { 
    var cell = sheet.getRange(index, dateCol + 1);
    var date = Utilities.formatDate(new Date(), timezone, timestamp_format);
    cell.setValue(date);
  }
}
tehhowch
  • 9,645
  • 4
  • 24
  • 42
emmalo
  • 35
  • 3

1 Answers1

0

As long as each date that you want is on Wednesday, format today's date by the date's week day number, find the difference from today() and next 3 - wednesday

function onEdit(event)
{ 
  ...
  var today = new Date();

  // Today as day of week by number - 1 Monday...0 Sunday
  var day_of_week = today.getDay();

  // Find the diff in days until next Wednesday
  var diff = day_of_week > 3 ? 10 - day_of_week : 3 - day_of_week;

  // Add `diff` amount of days to today
  today.setDate(today.getDate() + diff);
  ...

  cell.setValue(today);
}

edit: Updated adding days to dates - from Add days to JavaScript Date. Tested today.setDate(today.getDate() + diff); with both leap year and DST dates/times. Seems to be the most elegant working solution for this particular use.

random-parts
  • 2,137
  • 2
  • 13
  • 20
  • `new Date(today.getTime() + diff * 3600000 * 24)` is not a good way of adding days since not all days are 24 hours long where daylight saving is observed. See [*Add days to JavaScript Date*](https://stackoverflow.com/questions/563406/add-days-to-javascript-date). – RobG Oct 04 '17 at 05:40
  • @RobG Good points; I had completely overlooked DST. Updated based on a solution in that link. Thanks! – random-parts Oct 04 '17 at 15:49