0

Given five times, Pacific, Mountain, Central, Eastern and UTC...

enter image description here

I want to enter a time in any one of those, not the same one every time, and have the others adjust. For instance, if entering "2:00 PM" in the Pacific time zone (for one of the columns), the other four should adjust. Or, if entering whatever time in another field, the other four fields would change accordingly. Is this possible? How?

  • You can do that with an event-triggered macro using VBA in Excel. Not sure about Sheets. – Ron Rosenfeld Jul 12 at 19:00
  • Sorry, I realize I need this to be done in sheets as it will be a public document. Thank you though! – adamaero Jul 12 at 19:03
  • 1
    Welcome to Web Applications. Please add a brief description of your search/research efforts as is suggested in How to Ask. NOTE: In Google Sheets macros are created using the macro recorder but they are pretty limited, usually it's better to write the code directly in Google Apps Script. – Rubén Jul 12 at 20:52
  • If I found something relevant I would've included it. Spoiler, I did not find anything relevant. Since then, I spent an hour manually making a catalog of the most common times. – adamaero Jul 12 at 20:59
  • Is Daylight savings time an important issue for you? – Tedinoz Jul 13 at 4:11
1

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:

Sample layout


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");
  
  }
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.