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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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
itsmevicAuthor Commented:
That's correct.  :)
gowflowCommented:
Are these location exactly as timezone description can you post a sample workbook ?
gowflow
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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

Sample.xlsx
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
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
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.
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
itsmevicAuthor Commented:
Yes, just the three locations noted.
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
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.
gowflowCommented:
correct time as regards to what time ??? sorry It is not clear what you want
gowflow
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

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!
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
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.