time zone calculator?

[Cupid]

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.

 

[Barry Katcher]

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
	A	B	C	D	E	F	G	H	I	J	K
4		Pacific	Mountain	Central	EST	Atlantic	Spumoni	Quiche	Pasta	Fongool	Europe
5	Current	6:06 AM	7:06 AM	8:06 AM	9:06 AM	10:06 AM	11:06 AM	12:06 PM	1:06 PM	2:06 PM	3:06 PM
6	Input	12:56 PM	1:56 PM	2:56 PM	3:56 PM	4:56 PM	5:56 PM	6:56 PM	7:56 PM	8:56 PM	9:56 PM
7
8					Current
9					Time
10					Central
11					8:06 AM
12
13					Input
14					Time
15					Pacific
16					12:56 PM
Time Zones

 

[Cupid]

Thats Perrrfect,

Thanks ever so much 8)

 

[Cupid]

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
BANGLADESH
BANJA LUKA
BARBADOS
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

 

[Barry Katcher]

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
	A	B	C	D	E
1	Country	+/-		Denmark	Same
2	US Central	7 hours earlier		Germany	1 hour later
3	Denmark	Same		US Eastern	6 hours earlier
4	US Pacific	9 hours earlier		US Central	7 hours earlier
5				US Rock Mt.	8 hours earlier
6				US Pacific	9 hours earlier
Sheet3

 

[Cupid]

Your a Star 10 Team points *grin* :D