Google Apps Script [Spreadsheet Date]

Question

I have 2 columns 'StartDate' and 'EndDate'

Is it possible to do validation to check that 'EndDate' cannot be earlier than 'StartDate', vice versa? I don't know if validation is to be done on the spreadsheet itself, or through script.

Answer 1

You could also convert the dates to integers and compare the values. if(int(enddate-startdate<0,do something,do something else)

Answer 2

You can easily do it using the Spreadsheet built-in data validation feature (menu Data > Validation). Using a "Custom Formula" validation, and simply typing =B1>A1 (assuming your 2 columns are the 1st 2).

Or do it in a script using the onEdit trigger, which is also easy, but not as much as the above.

--edit

If you also have to check if the cell is a Date, you could use the following formula as data validation:

=AND(DateValue(B1),B1>A1)

It would look like this using an onEdit trigger:

function onEdit() {
  var s = SpreadsheetApp.getActiveSheet();
  if( s.getName() !== 'Sheet1' ) return; //check if it's the sheet we want to monitor
  var r = s.getActiveRange();
  if( r.getColumn() !== 2 ) return; //check if it's the column we're interested
  var v = r.getValue();
  if( typeof v !== 'object' ) {
    r.setValue('');
    return SpreadsheetApp.getUi().alert('Enter a valid date!');
  }
  var vA = r.offset(0, -1).getValue();
  if( typeof vA === 'object' ) {
    if( vA.getTime() >= v.getTime() ) {
      r.setValue('');
      return SpreadsheetApp.getUi().alert('Enter a date bigger than start date!');
    }
  } //what to do if there is no date in column A?
}

Note that this code does not check if the start date is changed, and therefore it can be changed afterwards to a value greater than the end date. But if you go with this code solution you should be able to take it from here.