time zone calculator?

Cupid

New Member
Joined
Jul 20, 2006
Messages
36
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

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Barry Katcher

Well-known Member
Joined
Feb 25, 2002
Messages
4,053
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
 

Cupid

New Member
Joined
Jul 20, 2006
Messages
36
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

Well-known Member
Joined
Feb 25, 2002
Messages
4,053
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
 

Forum statistics

Threads
1,141,734
Messages
5,708,166
Members
421,549
Latest member
Dtcfire

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top