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

Given five times, Pacific, Mountain, Central, Eastern and UTC... 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
• 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

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

// 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];
//Logger.log("DEBUG: the edited location is "+locname);

switch (locname) {
case 'Pacific':
//Logger.log("DEBUG: entering Pacific switch");
var ptdiff = timediffs[erow-1];
var mtdiff = timediffs[erow];
var ctdiff = timediffs[erow+1];
var etdiff = timediffs[erow+2];
var utcdiff = timediffs[erow+3];
// 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");

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];
var mtdiff = timediffs[erow-1];
var ctdiff = timediffs[erow];
var etdiff = timediffs[erow+1];
var utcdiff = timediffs[erow+2];
// 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")

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];
var mtdiff = timediffs[erow-2];
var ctdiff = timediffs[erow-1];
var etdiff = timediffs[erow];
var utcdiff = timediffs[erow+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")

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];
var mtdiff = timediffs[erow-3];
var ctdiff = timediffs[erow-2];
var etdiff = timediffs[erow-1];
var utcdiff = timediffs[erow];
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")

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];
var mtdiff = timediffs[erow-4];
var ctdiff = timediffs[erow-3];
var etdiff = timediffs[erow-2];
var utcdiff = timediffs[erow-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")

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

}
``````