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!

 

[Idea1390]

Thank you for the idea, I combined all the information in one cell and it works perfect. I created a custom cell format and was able to get the result

 

[Idea1390]

Hi All,
Now that the time zone converter is working just the way I wanted, I thought of adding an additional feature. I have the start and end dates for different regions. I am trying to do conditional formatting where the condition is like this:
If Start Date OR End Date is between 9:00 AM to 06:00 PM, the region name should turn RED. i,e; If the condition is true for Europe, the Europe cell should become RED. The current format of the cells are Custom Format (as per my needs).
Thanks in advance.

 

[barry houdini]

If start date/time is in A2 and end date/time in B2 you could use a formula like this in conditional formatting

=OR(AND(MOD(A2,1)>="09:00"+0,MOD(A2,1)<="18:00"+0),AND(MOD(B2,1)>="09:00"+0,MOD(B2,1)<="18:00"+0))

 

[Idea1390]

That works Thanks a lot Barry. I tried a similar formula but did not use the MOD function in it.

 

[honkin]

Hi
I have been just reading through this whole thread, but for the life of me cannot work out how to do what I would like.
I am wanting to indicate what time locally I would need to do Facebook business posts to optimise the results.

Currently in C5 I have typed the text 7am and then formatted it as time. I have the time difference for the relevant timezone, which is in C9. In this example, it is -11. I am just trying to have the result cell show 8pm, which is 7am minus 11 hours.

I have tried some of the formulas in this thread, but keep getting no result. I am sure it is just the nut holding the wheel, but may I ask for some assistance in this simple task?

Oh, I do not need any dates or even days....just times.

regards

honkin