Repair and Clean-Up

I am looking to change the time zone selection column to reflect only these...

CST (UTC-6)
MST (UTC-7)
PST (UTC-8)
AST (UTC-9)
HAST (UTC-10)
EST (UTC+1)
EET (UTC+2)
WST (UTC+8)
AEST (UTC+10)

I am looking to have the Current Time column reflect this format (ex. 5:00 PM TU EST).
If this next question needs to be part of a new question then that is OK and I will happily oblige.  

Thank you for your help here >> https://spreadsheets.google.com/ccc?key=0ApGg6c9aeywQdGZ5VzcxZHljZkJjQmJ5Y2hJMFgzYUE&hl=en&authkey=COOO6JsO#gid=0
rtod2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

>> ex. 5:00 PM TU EST

What does the "TU" mean and where does it come from?
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
The time zone selection is based on data validation with the source data located in Sheet3, column A. Just change the entries in this sheet and the data validation options presented will change, too.

Then use a concatenation and vlookup like currently in place, but concatenate only the time value and the looked up time code.

cheers, teylyn
0
rtod2Author Commented:
Monday = M
Tuesday = TU
Wednesday = W
Thursday = TH
Friday = F
Saturday = SA
Sunday = SU

If I change the values as suggested, I get errors.  I really need help with this.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
I changed the CST data item in Sheet3 and chose that item in the drop down box in Sheet 1. The formulas did not break. See my last saved version.

With regards to the weekday shortenings: There is currently a quite elaborate formula in place to arrive at the three letter weekday text. It will be quite an exercise to change that to a one or two letter short code.

I'd leave it as it is.

cheers, teylyn
0
rtod2Author Commented:
OK, I can leave it for now.  The part I am concerned about is the format in column C.  I am telling you that when I change something there, I get an error.  Please assist if possible.  I'll leave this question open.
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Look at C5. I have chosen the new version of the value. There is no error in any other cell of the row.

When you change the values on Sheet3, you also need to adjust the selected value in Sheet1.

For example: When you change

(GMT-05:00) Eastern Time (US & Canada)

on Sheet3 to

EST (UTC+1)

then you need to go back to Sheet1 and replace all the selections of (GMT-05:00) Eastern Time (US & Canada) with EST (UTC+1). If you don't change the values in both sheets, you will see errors, because there are several formulas that look up the value in column C.

cheers, teylyn
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Note, the above is an example. I do not know whether or not EST is the same as "Eastern Time (US & Canada)"

You will need to do your own mapping. The crucial thing is that you have the same values in Sheet1 and Sheet3.
0
rtod2Author Commented:
Not working for me.  I get errors.  Please change if possible.  I'll leave this alone for a couple of days and see if interest can be re-generated.

All I am looking for are these.  The rest are unimportant.

CST (UTC-6)
MST (UTC-7)
PST (UTC-8)
AST (UTC-9)
HAST (UTC-10)
EST (UTC+1)
EET (UTC+2)
WST (UTC+8)
AEST (UTC+10)

Thank you sir for trying.
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Is there another version of the sheet where you put in the codes? The link in the original question only shows the changes I already mentioned.

I'd like to see the errors you get.
0
rtod2Author Commented:
just says error in the cell.

I am looking to change Column C to reflect only these 9 values.

CST (UTC-6)
MST (UTC-7)
PST (UTC-8)
AST (UTC-9)
HAST (UTC-10)
EST (UTC+1)
EET (UTC+2)
WST (UTC+8)
AEST (UTC+10)
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Please understand that if you cannot show me the file, then I cannot examine it and cannot see where things go wrong.

Post the file or a link to the file with your data put in that shows the errors.

Is that so hard to understand?
0
rtod2Author Commented:
It's a google sheet online format.  That's all I have.  Sorry.
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
But how can I access it? If I click on the link in your question I only get the original file. I need to see the file with your edits and the errors.
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
The file in the link above was saved an hour ago. Maybe you need to make your changes and save it again?
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
maybe this will work better with Google Docs Motion

http://www.google.com/google-d-s/promos/motion.html

:-)))
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
rtod, unless you publish the spreadsheet with your errors, I doubt anyone can do anything about it.

Over to you.
0
rtod2Author Commented:
ok
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Spreadsheets

From novice to tech pro — start learning today.

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.