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!

 

[Zerxes]

Hi there.

I have been trying to make a similar spreadsheet, however with all timezones from -12 to +12.

The time calculation works perfectly.. however when it comes to the Day calculation, the formula

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

works fine for displaying the previous day, but what about if I also want it to change to the following day (for instance, if i've entered Sunday 8pm GMT, then in Australia it would be Monday 7am).

Your expertise is much appreciated!! Thanks!

 

[barry houdini]

Hello Zerxes, welcome to MrExcel,

Try this version

=TEXT(MATCH($D5,TEXT({1,2,3,4,5,6,7},"dddd"),0)+INT($C5+C10/24),"dddd")

 

[Zerxes]

Thanks, it's a privelage to be here!

Works like a charm.

I would call you a genius, but I suppose this is easy stuff for you!

I'll invest some time and attempt to figure out how it works.

Thanks again - and perhaps i'll be back with another question before i'm done with this

 

[antz99]

Hi Barry,

I was wondering if you could help me. I'm now also trying to set up a very simple timezone calculator for the purpose of booking global conf calls.

I want to input a time, no matter what timezone and update all other timezones accordingly. Is this possible? Is there are sort of template that you may have available for this?

I'm unfortunately not an excel expert, so any simplistic help would be most useful.

Many thanks in advance for your help.

Anthony

 

[Bandogge]

Hello Barry, jees I thought I knew a little excel until I came here. I have a small call center and am trying to make a time zone spreadsheet with US States. I know the =NOW() formula to get current time, but I need to know the formula that will make the states draw from this cell and calculate their current times. I tried just adding plus times, but it moves the date forward not the time. Also how do I get =NOW() to continuosly recalculate itself,

Thanks, Much appreciated if you can help.

 

[jpalfy]

I have done something a little simplier. I have the need to convert from Mountain to Eastern or Central to Eastern so what I have done is

Cell A3 place the Mountain Time formatted as customm/d/yyy h:mm

Then in Cell B3 use the following formula =Sum((-120/1440)+A3)
The 1440 is the number of minutes in the day
The -120 is the number of minutes difference between the two time zones

Once you have the formula you can change the -120 to the difference between the two time zones.

Again mine is more simplistic because I always deal with the same time zones and need to enter in both time zones

 

[daspprem]

Hey Barry,

I need the date as well as time in the same cell to converted to a different time zone. Can you please help with this.
<TABLE style="WIDTH: 330pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=440><COLGROUP><COL style="WIDTH: 110pt; mso-width-source: userset; mso-width-alt: 5339" width=146><COL style="WIDTH: 143pt; mso-width-source: userset; mso-width-alt: 6985" width=191><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3766" width=103><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow; WIDTH: 110pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20 width=146>Start Time : CST</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: yellow; WIDTH: 143pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=191>End Time : CST</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: yellow; WIDTH: 77pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=103>Start Time : IST</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67 height=20 align=right>20/01/2012 13:00</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67 align=right>20/01/2012 13:30</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl68></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67 height=20 align=right>20/01/2012 13:30</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67 align=right>20/01/2012 14:00</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl68></TD></TR></TBODY></TABLE>

I tried a lot but was not successful please help me with this, this would be great help for my work.

 

[CLEList]

So what do we do about Arizona? Is there a way to automate when it doesn't change with DST/MST?

 

[Idea1390]

Hi Barry,

I am creating a time zone converter with manual entry for date, time and day.

When I manually enter a date, time and day, I am successful in getting the corresponding new time and day name for different time zones but unsuccessful for the date. The above mentioned formula works perfect for the day. I was wondering if there is any formula to get the date ? Currently I get random dates.

Thanks in advance.

 

[barry houdini]

Do you need to split it up, it's probably easier to have all the information in one cell, e.g. enter a time/date in A2 and format to show day, date and time, e.g. the current time/date for me formatted as

ddd dd/mm/yyyy

would display as Mon 22/06/2015 11:40

then if I used this formula in another cell to add, for example, 15 hours

=A2+"15:00"

and format the same way I get

Tue 23/06/2015 02:40