Excel 2010: Time Zone Formula

Hello Experts!

    Quick question.  I have an Excel Workbook and within that Workbook a Worksheet that contains the following:

    Column A                          Column B                 Column C
1   Location                        Current Time           Current Date  <Header Row>
2   Budapest, Hungary         <Time>                       <Date>
3.  Manila, Philippines          <Time>                       <Date>
4.  Dallas, Texas                     <Time>                       <Date>

    Simply put, I'd like a macro or a formula that I can use that will input current times and dates for these places when the Worksheet is opened within the Workbook.  Any help with this is GREATLY APPRECIATED!   Thanks.
itsmevicAsked:
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.

gowflowCommented:
Let me get this correctly. like if you open the worksheet and the time now is 5:37 at your location you want that time to be converted depending on the location in Column A ?

gowflow
0
itsmevicAuthor Commented:
That's correct.  :)
0
gowflowCommented:
Are these location exactly as timezone description can you post a sample workbook ?
gowflow
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

itsmevicAuthor Commented:
The locations are exact.
0
gowflowCommented:
ok pls post a sample workbook
gowflow
0
itsmevicAuthor Commented:
Hi gowflow - Please see attached.

Sample.xlsx
0
Saurabh Singh TeotiaCommented:
I believe this is what you are looking for..I applied formula in cells highlighted in yellow basis of your input to dallas time..

Saurabh...
Sample.xlsx
0
gowflowCommented:
I thought you wanted to have all time zones coutries and were working on something but you came up with 3 countries of which 1 is the local so no need for anything just simple formula like Saurabh mentioned.

Please advise if this is what you need or ... ??
gowflow
0
itsmevicAuthor Commented:
The ideal solution would be for the worksheet to auto update all time zones once clicked on.  Not just based off of the Dallas, Texas time zone.
0
gowflowCommented:
Fine but you only need the locations you have mentioned ??? or you have more ?? if more we need the list of all your timezones as basically there are over 1000 in the world and we need to know what you want exactly.
gowflow
0
itsmevicAuthor Commented:
Yes, just the three locations noted.
0
gowflowCommented:
ok so then let me get this correctly:

Your aim then is to be able to input a date at any location and see what would be the time at the other 2 locations ? is that what you want ?

If my assumption is correct then please look at the attached file. All the shaded cells can be input (except for sure cell A2 where you select a region from the dropdown) you input in the green the date and time and it will automatically convert in the blue table.

You can add more cities and regions for this you need to do the following:

in A input the name of the city under thee last city
in B and C simply drag the formula from the previous city
in D just input the GMT +/- for that city.

Once done you will need to expand the name ranges in the menu Formula select Name Manager you will see Location and data click on each one and replace the ending row by the new row and press on the ok to save do this for both data and Location and you are set to include more regions.

Let me know.
gowflow
TimeZone-Ref.xlsm
0
itsmevicAuthor Commented:
Thanks for your response gowflow and the example you provided.  In simple terms, all i'm needing is this:

1.)   Open the worksheet.
2.)   The three locations noted on the worksheet, automatically update with the correct times for those
        time zones.
0
gowflowCommented:
correct time as regards to what time ??? sorry It is not clear what you want
gowflow
0
gowflowCommented:
ok I slightly modified the file to pickup the current date and time you only need to choose the location on A2. Don't know if this what you want let me know
gowlfow
TimeZone-Ref-V01.xlsm
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
itsmevicAuthor Commented:
That is PERFECT gowflow!  Thank you so much for all your help with this!
0
gowflowCommented:
I noticed a small bug here is an updated. This is due when you put the formula now and today and add them up we get a weird value reason why in the formula we need to only take the B2. This is updated in this version. Sorry.
gowflow
TimeZone-Ref-V02.xlsm
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
Microsoft Excel

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.