Google spreadsheets: Possible to enter in one of any of any given time and have others adjust accordingly?

You have start and end times spread over five timezones. You want to enter a start time for any timezone and have each of the start times (and the respective end times) for the other timezones updated accordingly.

It is quite likely that there are many ways to solve your question; please consider this as just one approach.

Assume that you have a layout like this:

---

The solution is in two parts:
End times

• There is a formula =A1+(2)/24 in cell C1. This formula was copied into rows 2, 3, 4 and 5. The effect is that each end time is 2 hours later than the start time.

Start times

• the script is written as an onEdit trigger.
• the key data ranges are written as variables.
• the Time differences between timezones is written as a variable.
• the script relies on an IF statement to limit execution to a specific sheet, specific column and specific range of rows.
• a switch method, based on the location name from Column D, triggers execution for the specific timezone
• times are updated using setFormula. It is a simple add or subtract hours to the entered time value; this by-passes the spreadsheet's timezone and any issues about formatting dates.
• all values and formula are converted to values only.

The code in the switch statement could be probably be made more efficient. But the OP can address this as a secondary issue.

---

function onEdit(e){
  
  //Tedinoz ref:wa14389907
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // rows applying to time calcs
  var timescolumn = 1;
  var timerowfirst = 1;
  var timerowlast = 5;
  var timesheet = "Time_Convert";
  var timediffs = [["Pacific",-8],["Mountain",-6],["Central",-5],["Eastern",-4],["UTC",0]] 
  
  // get parameters using event objects
  var editrange = e.range;
  var esheet = editrange.getSheet().getName();
  var ecolumn = editrange.getColumn();
  var erow = editrange.getRow();  
  // Logger.log("DEBUG: edited column: "+ecolumn+" ,row: "+erow+", sheet: "+esheet)

  // test for edit in the correct column, row and sheet
  if (esheet === timesheet && ecolumn === timescolumn && (erow >= timerowfirst || erow <= timerowlast )){
    // Logger.log("DEBUG: edit on the sheet and in the column and in the rows");
    var sheet = ss.getSheetByName(timesheet)
    var datarange = sheet.getRange(1,1,5,4);
    var datavalues = datarange.getValues();
    // Logger.log("DEBUG: data range  = "+datarange.getA1Notation());
    var locname = datavalues[erow-1][3];
    //Logger.log("DEBUG: the edited location is "+locname);
    
    switch (locname) {
      case 'Pacific':
        //Logger.log("DEBUG: entering Pacific switch");
        var ptdiff = timediffs[erow-1][1];
        var mtdiff = timediffs[erow][1];
        var ctdiff = timediffs[erow+1][1];
        var etdiff = timediffs[erow+2][1];
        var utcdiff = timediffs[erow+3][1];
        // Logger.log("DEBUG: ptdiff = "+ptdiff+", and mtdiff = "+mtdiff+", and ctdiff = "+ctdiff+", and etdiff = "+etdiff+", and utcdiff = "+utcdiff)
        // Mountain
        sheet.getRange(2,1).setFormula("=A1+(-"+ptdiff+"+"+mtdiff+")/24");
        // Central
        sheet.getRange(3,1).setFormula("=A1+(-"+ptdiff+"+"+ctdiff+")/24");
        // Eastern
        sheet.getRange(4,1).setFormula("=A1+(-"+ptdiff+"+"+etdiff+")/24");
        // UTC
        sheet.getRange(5,1).setFormula("=A1+(-"+ptdiff+"+"+utcdiff+")/24");

        SpreadsheetApp.flush();
        
        sheet.getRange(1,1,5).copyTo(sheet.getRange(1,1,5), {contentsOnly:true});
        // Logger.log("DEBUG: exiting Pacific switch");
        break;
        
      case 'Mountain':
        // Logger.log("DEBUG: entering Mountain switch");
        var ptdiff = timediffs[erow-2][1];
        var mtdiff = timediffs[erow-1][1];
        var ctdiff = timediffs[erow][1];
        var etdiff = timediffs[erow+1][1];
        var utcdiff = timediffs[erow+2][1];
        // Logger.log("DEBUG: ptdiff = "+ptdiff+", and mtdiff = "+mtdiff+", and ctdiff = "+ctdiff+", and etdiff = "+etdiff+", and utcdiff = "+utcdiff)
        // Pacific
        sheet.getRange(1,1).setFormula("=A2+(-"+mtdiff+"+"+ptdiff+")/24");
        // Central
        sheet.getRange(3,1).setFormula("=A2+(-"+mtdiff+"+"+ctdiff+")/24");
        // Eastern
        sheet.getRange(4,1).setFormula("=A2+(-"+mtdiff+"+"+etdiff+")/24");
        // UTC
        sheet.getRange(5,1).setFormula("=A2+(-"+mtdiff+"+"+utcdiff+")/24")
        
        SpreadsheetApp.flush();
        
        sheet.getRange(1,1,5).copyTo(sheet.getRange(1,1,5), {contentsOnly:true});
        // Logger.log("DEBUG: exiting Mountain switch");
        break;
        
      case 'Central':
        // Logger.log("DEBUG: entering Central switch");
        var ptdiff = timediffs[erow-3][1];
        var mtdiff = timediffs[erow-2][1];
        var ctdiff = timediffs[erow-1][1];
        var etdiff = timediffs[erow][1];
        var utcdiff = timediffs[erow+1][1];
        // Logger.log("DEBUG: ptdiff = "+ptdiff+", and mtdiff = "+mtdiff+", and ctdiff = "+ctdiff+", and etdiff = "+etdiff+", and utcdiff = "+utcdiff)
        // Pacific
        sheet.getRange(1,1).setFormula("=A3+(-"+ctdiff+"+"+ptdiff+")/24");
        // MOUNTAIN
        sheet.getRange(2,1).setFormula("=A3+(-"+ctdiff+"+"+mtdiff+")/24");
        // Eastern
        sheet.getRange(4,1).setFormula("=A3+(-"+ctdiff+"+"+etdiff+")/24");
        // UTC
        sheet.getRange(5,1).setFormula("=A3+(-"+ctdiff+"+"+utcdiff+")/24")
        SpreadsheetApp.flush();
        
        sheet.getRange(1,1,5).copyTo(sheet.getRange(1,1,5), {contentsOnly:true});
        //Logger.log("DEBUG: exiting Central switch");
        break;
        
      case 'Eastern':
        // Logger.log("entering Eastern switch");
        var ptdiff = timediffs[erow-4][1];
        var mtdiff = timediffs[erow-3][1];
        var ctdiff = timediffs[erow-2][1];
        var etdiff = timediffs[erow-1][1];
        var utcdiff = timediffs[erow][1];
        Logger.log("ptdiff = "+ptdiff+", and mtdiff = "+mtdiff+", and ctdiff = "+ctdiff+", and etdiff = "+etdiff+", and utcdiff = "+utcdiff)
        // Pacific
        sheet.getRange(1,1).setFormula("=A4+(-"+etdiff+"+"+ptdiff+")/24");
        // MOUNTAIN
        sheet.getRange(2,1).setFormula("=A4+(-"+etdiff+"+"+mtdiff+")/24");
        // Central
        sheet.getRange(3,1).setFormula("=A4+(-"+etdiff+"+"+ctdiff+")/24");
        // UTC
        sheet.getRange(5,1).setFormula("=A4+(-"+etdiff+"+"+utcdiff+")/24")
        SpreadsheetApp.flush();
        
        sheet.getRange(1,1,5).copyTo(sheet.getRange(1,1,5), {contentsOnly:true});
        Logger.log("exiting   switch");
        break;
        
      case 'UTC':
        // Logger.log("DEBUG: entering UTC switch");
        var ptdiff = timediffs[erow-5][1];
        var mtdiff = timediffs[erow-4][1];
        var ctdiff = timediffs[erow-3][1];
        var etdiff = timediffs[erow-2][1];
        var utcdiff = timediffs[erow-1][1];
        // Logger.log("DEBUG: ptdiff = "+ptdiff+", and mtdiff = "+mtdiff+", and ctdiff = "+ctdiff+", and etdiff = "+etdiff+", and utcdiff = "+utcdiff)
        // Pacific
        sheet.getRange(1,1).setFormula("=A5+(-"+utcdiff+"+"+ptdiff+")/24");
        // MOUNTAIN
        sheet.getRange(2,1).setFormula("=A5+(-"+utcdiff+"+"+mtdiff+")/24");
        // Central
        sheet.getRange(3,1).setFormula("=A5+(-"+utcdiff+"+"+ctdiff+")/24");
        // Eastern
        sheet.getRange(4,1).setFormula("=A5+(-"+utcdiff+"+"+etdiff+")/24")
        SpreadsheetApp.flush();
        
        sheet.getRange(1,1,5).copyTo(sheet.getRange(1,1,5), {contentsOnly:true});
        // Logger.log("DEBUG: exiting UTC switch");
        break;
        
        
        
      default:
        // Logger.log("DEBUG: Sorry, no location found");
}


}
  else{
   // Logger.log("DEBUG: edit wasn't in the right range");
  
  }