Timezone Conversion

**denroi** · 09-21-2010, 05:27 AM

Hi

I am totally new to VBA EXCEL and trying to understand it but i got stuck at one problem.
I am using Excel 2007.

My problem is regarding conversion of timezones(GMT, EDT, IST etc).
i have cells B1 to M1 with names of timezones and B2 to M2 with their time differences w.r.t GMT
GMT is put up in B column
i know the formula used for conversion if GMT is fixed and all other rows are changed according to its value.
Formula is: (for C3 value)
=MOD($B$3-(C2/24)*-1,1)
But i want the values to change depending upon any value.
ie if we are changing any cells value , it should adjust all the other cell's values in that row
and also if user is entering any new cells entry say for IST, other cells should also get filled as per

i have attached my excel file for rows and columns.

i have searched a lot but nothing found.
Any help would be appreaciated.

Thanks

**arthurbr** · 09-21-2010, 05:35 AM

Hi and welcome to the board,
as nobody likes to retype data, please add a sheet instead of a picture.

Thank you

**Marcol** · 09-21-2010, 06:34 AM

Hi denroi

Welcome to the forum.

I agree with Arthur, why post a picture of your workbook? You obviously have one that you could have posted

Try this demo workbook.
Select your time zone from the dropdown in A2 then enter your time in B2

**denroi** · 09-21-2010, 07:02 AM

thanks marcol
can u please explain the function u applied i mean y applying B2 in it..?
i am sorry , may be i am asking silly question. but still i want to understand it
thanks.

**Marcol** · 09-21-2010, 07:53 AM

The initial formula is in C3

Please Login or Register  to view this content.

$A$3 is the cell that you enter the time you want to convert, this is "anchored" to both row and column.

MATCH($A2,$C$1:$N$1,0), this returns the position in the range $C$1:$N$1 of the time zone that you choose in B2.

OFFSET($B$2,0,MATCH($A2,$C$1:$N$1,0)) returns the time difference relative to GMT, by using B2 as a reference we can simply use the value returned by the match formula.

The rest is just arithmetic to calculate the relative differences between your chosen zone and the others.

I have added a break down of the formula to the amended workbook attached.

I have also added this formula to row 5

Please Login or Register  to view this content.

It is an easier and non-volatile way to do the same thing, I just hammered down the first thing that came to my head in my first post.

Hope this helps

**denroi** · 09-22-2010, 12:04 AM

thanks a tonne Marcol.
I have completed my task.
i have added a button too which will autogenerate the nxt row having its own drop down cell in column A.
Thanks!!!

**Ghassan Nabeel** · 03-02-2021, 09:58 AM

Hi everyone

if we have the city name in cell A1 how to get GMT result from the web into cell B2 ?

I mean I want to get the info from the internet immediately .
is there any function to do it ?

**AliGW** · 03-02-2021, 10:19 AM

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread