Timezone Calculating Formula

[brianclong]

Hi I'm trying to create a sheet with different timezones around the world in which my company operates. The idea is to enter a time (for example you want to set up an international phone conference and you want to see the local times in several different countries) and see that time in all the other countries.

I have created the sheet but this formula stops working when I enter times around 12:00ish. =$C$4+(C7/24)

PS - If I put a "*-1" into the end of the formula, it calculates hours forward of my time (in other words, at 3:00 AM Japan time, It's 1:00 PM USA EST - but the formula would say 5:00 PM).

For example:

Excel Workbook
	B	C	D	E	F	G	H	I	J	K
4	JAPAN TIME:	12:00 PM
6		USA - Eastern	USA - Central	USA - Mountain	USA - Pacific	Argentina	Brazil	China - Hong Kong	China - Main	China - Taiwan
7	Time Difference:	-14	-15	-16	-17	12	-14	-1	-1	1
8
9
10		Czech Republic	Dominican Republic	Germany	Latvia	Malaysia	Mexico	Portugal	Puerto Rico	Singapore
11	Local Times:	##############	################	##############	##############	12:00 AM	##############	11:00 AM	11:00 AM	1:00 PM
Timezone Calculator

Can anyone tell me what I'm doing wrong? Thank you!

 

[stanleydgromjr]

brianclong,

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


Have a great day,
Stan

 

[brianclong]

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.

 

[brianclong]

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

 

[edvwvw]

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

 

[barry houdini]

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

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

 

[brianclong]

Geniuses!!

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

 

[brianclong]

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

 

[barry houdini]

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)

 

[brianclong]

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!