# time zone calculator?

#### Cupid

##### New Member
Hi gang

is there a macro or an automated system in excell to help me work out the "time zones" in other countrys, compaired to a current "uk" time when entered ?

eg call A1 I enter the current UK time, and in cells b1 through to b# it gives me the times zones for the different countrys.

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Hey, Cupid. Try playing around with something like this:

B5:K6 is formated as Time
E5 is the current time and the formula is: =NOW()
E6 is any time you input - no formula
Formula in D5, copied down and to the left, is: =E5-0.041667
Formula in F5, copied down and to the right, is =E5+0.041667

E10 and E15 are manually input
Data Validation for E10 and E15 is: Allow: List…..Source: =B4:K4
Formula in E11 is =HLOOKUP(E10,\$B\$4:\$K\$6,2,FALSE)
Formula in E16 is: =HLOOKUP(E15,\$B\$4:\$K\$6,3,FALSE)
KEEPERS.xls
ABCDEFGHIJK
4PacificMountainCentralESTAtlanticSpumoniQuichePastaFongoolEurope
5Current6:06 AM7:06 AM8:06 AM9:06 AM10:06 AM11:06 AM12:06 PM1:06 PM2:06 PM3:06 PM
6Input12:56 PM1:56 PM2:56 PM3:56 PM4:56 PM5:56 PM6:56 PM7:56 PM8:56 PM9:56 PM
7
8Current
9Time
10Central
118:06 AM
12
13Input
14Time
15Pacific
1612:56 PM
Time Zones

Thats Perrrfect,

Thanks ever so much 8)

I see how this works, having it subtract and add an hourly rate..

is there a way i could add country zones.. for example

UK 12 noon. is US timezone
uk 12 noon, is Japan timezone ? I have a rather large list here at work, that gives me all the + and - times to a UK zone ... 30+ global zones so i could work it out manually, but i would be great to have a global version stemming from just the one uk time.
short list of examples are like follows

A1-A# down

BAHAMAS
BAHRAIN
BALEARIC ISLANDS
BANJA LUKA
BELARUS
BELGIUM
BELIZE
BENIN
BERMUDA
BHUTAN
BOLIVIA
BOSNIA-HERZEGOVINA
BOTSWANA
BRAZIL
BRUNEI
BULGARIA
BURKINA
BURMA ( MYANMAR )
BURUNDI

B1-B# down

5 HRS EARLIER
3 HRS LATER
1 HR LATER
6 HRS LATER
1 HR LATER
4 HRS EARLIER
2 HRS LATER
1 HR LATER
6 HRS EARLIER
1 HR LATER
4 HRS EARLIER
6 HRS LATER
4 HRS EARLIER
1 HR LATER
2 HRS LATER
3 HRS EARLIER
8 HRS LATER
2 HRS LATER
SAME AS GMT
6.5 HRS LATER
2 HRS LATER

So can I assume its a simple gmt -+ # sum ? or is it not that simple

Great help so far though, it gives me a better understanding of how this works

I'm sure you could do this for some countries, such as Bermuda, that fall entirely in one time zone. But what about the U.S., which covers 4 time zones; 5 if you include Hawaii?

I guess you could use a VLOOKUP() formula to do something like this:
Book3
ABCDE
1Country+/-DenmarkSame
2US Central7 hours earlierGermany1 hour later
3DenmarkSameUS Eastern6 hours earlier
4US Pacific9 hours earlierUS Central7 hours earlier
5US Rock Mt.8 hours earlier
6US Pacific9 hours earlier
Sheet3

Your a Star 10 Team points *grin* :D

Replies
3
Views
208
Replies
1
Views
356
Replies
1
Views
500
Replies
1
Views
125
Replies
1
Views
257

1,219,812
Messages
6,150,367
Members
450,955
Latest member
rose8693

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back