Time Zone descriptor needed in Col B

Column B needs time zone descriptor also.

Example UTC -5:00 Central Time, US.  
Also the plus(+) or minus(-) sign in front of the zone should be stated explicitly.


my sheet >> https://spreadsheets.google.com/ccc?key=0ApGg6c9aeywQdGZ5VzcxZHljZkJjQmJ5Y2hJMFgzYUE&hl=en
potential examples >> http://www.michaelapproved.com/articles/timezone-dropdown-select-list/

Assistance is greatly appreciated.
rtod2Asked:
Who is Participating?
 
Curt LindstromConnect With a Mentor Commented:
This is a copy of the google spreadsheet which currently is working. It will not work properly in Excel bu all formulas used in this question and related questions can be seen.

Curt
Select-Time-Zone.xls
0
 
Curt LindstromCommented:
I think I got this working now. I'm just fixing up the descriptor table a little

Curt
0
 
rtod2Author Commented:
You're amazing!
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
Curt LindstromCommented:
Thanks, glad to help. Next question seems to be well in the hands of user 5111.

Curt
0
 
Saqib Husain, SyedEngineerCommented:
I need to understand this.
Are we updating the info at google?
Do we save it back there?
What happens if more than one users save it with different updates?

In any case I think we should be posting the results in this forum because the sheets may be deleted from google and this question looses its functionality.

Saqib
0
 
Curt LindstromCommented:
You are right Saqib. The only reason I haven't posted anything back was that question was accepted after I updated the google spreadsheet and before I got back to the question. The solution also becomes somewhat irrelevant in this case since it will only work on the google spreadsheet. Anyway using the now updated version:

Tables changed in sheet3 to add a description i A and Time Zones in B
enter in C2
=if(A2="","",mid(text(now()+vlookup(B2,sheet3!A:B,2,false)/24+5/24,"'Weekday 'EEE"),8,10))
and copy down
enter in E2
=if(A2="","",text(now()+vlookup(B2,sheet3!A:B,2,false)/24+5/24,"hh:mm:ss a"))
and copy down
enter in F2
=if(A2="","",text(now()+vlookup(B2,sheet3!A:B,2,false)/24+5/24,"HH:mm:ss"))
and copy down

Cheers,
Curt
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.