Timezone Calculating Formula

S

stanleydgromjr

Banned

Joined

Jul 30, 2006

Messages

3,656

• Jan 27, 2008

• #2

brianclong,

This may help (TimeZone conversion examples):
http://www.tushar-mehta.com/excel/timezone.htm


Have a great day,
Stan

 

Upvote 0

B

brianclong

Board Regular

Joined

Apr 11, 2006

Messages

168

• Jan 28, 2008

• #3

Hello. Thank you but I would rather do this with Excel formulas. I couldn't find the formulas on that VBA link, just the example. Thx though.

 

Upvote 0

B

brianclong

Board Regular

Joined

Apr 11, 2006

Messages

168

• Jan 28, 2008

• #4

Hello. Are there any ideas as to how to fix my formula? Any help would be appreciated!

 

Upvote 0

E

edvwvw

Active Member

Joined

Jan 30, 2007

Messages

278

• Jan 28, 2008

• #5

The reason that it wasn't working is that you also need the date for the calculations

Try the example below

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	JAPAN TIME:	12:00	Mon 28-Jan-08	*	*	*	*	*	*	*
2	*	USA - Eastern	USA - Central	USA - Mountain	USA - Pacific	Argentina	Brazil	China - Hong Kong	China - Main	China - Taiwan
3	Time Difference:	-14	-15	-16	-17	12	-14	-1	-1	1
4	*	*	*	*	*	*	*	*	*	*
5	*	Czech Republic	Dominican Republic	Germany	Latvia	Malaysia	Mexico	Portugal	Puerto Rico	Singapore
6	*	Sun 27-Jan-08	Mon 28-Jan-08	Sun 27-Jan-08	Sun 27-Jan-08	Tue 29-Jan-08	Mon 28-Jan-08	Mon 28-Jan-08	Mon 28-Jan-08	Mon 28-Jan-08
7	Local Times:	22:00	21:00	20:00	19:00	0:00	11:00	11:00	11:00	13:00
Sheet

You will see that I have also added the day of the week

edvwvw

 

Upvote 0

barry houdini

MrExcel MVP

Joined

Mar 23, 2005

Messages

20,825

• Jan 28, 2008

• #6

brianclong said:

Hello. Are there any ideas as to how to fix my formula?

Click to expand...

Hello Brian,

In default mode Excel doesn't like negative times, which is what is causing your problem, change formula to

=MOD($C$4+(C7/24),1)

format as time

 

Upvote 0

B

brianclong

Board Regular

Joined

Apr 11, 2006

Messages

168

• Jan 28, 2008

• #7

Geniuses!!

I used the MOD formula of course because that was the quickest but both great options. Thanks gents!

 

Upvote 0

B

brianclong

Board Regular

Joined

Apr 11, 2006

Messages

168

• Jan 29, 2008

• #8

Ed you touch on a good point. I would now like to add which day it is. In other words, 9:00 Monday in Japan is actually 7:00 PM Sunday in USA EST. Is there a simple formula to put the day under each country heading as I have below?

My days are wrong. If we base the table on 21:00 Japan time, Argentina should say "Friday" and China should say "Friday." The time formula is =MOD($C$5+(C10/24),1) dragged over.

Does anyone know a day formula that will work?

Excel Workbook
	B	C	D	E	F	G	H	I	J
4	??E	?@q	q	??@q	*	*	*	*	*
5	Meeting Time / Day:	21:00	Friday	*	*	*	*	*	*
6	*	*	*	*	*	*	*	*	*
7	*	A?J?[?@	A?J?[?@	A?J?[?@Rn	A?J?[?@m	A[`	uW	???`	?
8	*	*	*	*	*	*	*	*	*
9	Place - ?	USA - Eastern	USA - Central	USA - Mountain	USA - Pacific	Argentina	Brazil	China - HK	China - Main
10	Time Difference:	-14	-15	-16	-17	-11	-14	-1	-1
11	Time -	7:00	6:00	5:00	4:00	10:00	7:00	20:00	20:00
12	Day -	Friday	Friday	Friday	Friday	Thursday	Friday	Thursday	Thursday
13	*	*	*	*	*	*	*	*	*
Timezone Calculator

 

Upvote 0

barry houdini

MrExcel MVP

Joined

Mar 23, 2005

Messages

20,825

• Jan 29, 2008

• #9

Assuming D5 is just text and not a formatted date try this formula in C12 copied across

=IF($C5+C10/24<0,TEXT(MATCH(LEFT($D5,2),{"Mo","Tu","We","Th","Fr","Sa","Su"},0),"dddd"),$D5)

 

Upvote 0

B

brianclong

Board Regular

Joined

Apr 11, 2006

Messages

168

• Jan 29, 2008

• #10

Excellent! That works great. How does it work though? I thought that the Match(Left()) would return Friday and not the day before... Confused. Awesome though! Thanks!

 

Upvote 0