0

So I currently have a work schedule on Google Sheets, and we all work remotely, all over the country, so I want to add a dropdown menu that one can select to change the time zones of the current times.

At the moment I have a giant cluster of merged cells that just says, "Times in Pacific Time Zone", as well as a drop-down menu below it that has "Pacific," "Central," "Mountain," and, "Eastern," and what I would like to have happen is when someone clicks on Mountain, for example, it changes all of the times in specific, separated cells, to mountain time (default is pacific,) and then if they click another time zone, it switches to that one, as well as changing the "Times in Pacific Time Zone" to "Times in Mountain/Central/Eastern Time Zone".

Currently, the drop down menu is a data validation with "List of items" which are "Change Time Zone" (this one will do nothing but let people know what to click on) "Pacific" "Mountain" "Central" and "Eastern" in it, written as such: Change time Zone,Pacific,Mountain,Central,Eastern

I tried writing a script in a vacant cell, just to test it out, but I'm getting errors out the wazoo. Plus I'm worried with the way I'm currently writing this script, it'll add hours to the time zones but won't revert them back if you click something else on the drop down menu.

My current script in the vacant cell is as follows:
=if (P19 = "Pacific" AND (C5:D9 AND F5:G9 AND I5:J9 AND L5:M9 AND O5:P9 AND R5:S9 AND U5:V9 > 0),1,2)

I'm a sheets noob, please help.

This is what it looks like

1
  • Welcome to Web Applications. Google Sheets might have scripts and formulas. It's not clear if by "script" you actually are referring to a script or to a formula, in any case, the "script" has syntax errors. In example, in a formula, AND is a function but it can't be used with arrays of values, only with single values. Ref. AND function, developers.google.com/apps-script/guides/sheets. Tip: Search for questions about using "AND" boolean operations with array formulas. Jul 18, 2022 at 3:49

1 Answer 1

0

This answer shows how it is possible to adjust formulas according to a change in timezone - which is what you are trying to achieve. Whether your goal is a practical solution is a matter for you, but you need to be aware that the value of time related formulas are NOT constantly updated. They update as the spreadsheet is updated.


Sample

snapshot


  • I created a new spreadsheet with the Locale = "United States: and the Time Zone = "(GMT-07:00) Pacific Time. Cell B2 shows the current time.

  • I created a table of relevant timezones, their local GMT times, and an additional column showing the difference between the Timezone and the spreadsheet Timezone (Pacific). Range: A4:C8

  • Cell B10 contains a Data validation dropdown based on the values in the range A5:A8.

  • Cell B11 contains a formula to calculate the adjustment in any time formula.

    • If the dropdown value is "Pacific", then make no adjustment,
    • Else use vlookup to return the "Hours diff to Pacific" and divide by 24.
    • A named range "TimeAdjustment" was created for this cell.

How to build the time adjustment into formula

There is a sample in Cells G2 and G3. The Location has been adjusted to Eastern The formula is =now()+TimeAdjustment

  • This will automatically add/deduct the relevant number of hours based on TimeZone.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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