World Clock

S

SammyJJones

Ok, you guys helped be out big time with my last question!!! So here’
another.

I want A1 to = the current time (where I am)
B1 = Friends name
C1 = time diff in hours between us
D1 = What time it is where they are.


Is this possible???


Thanks in advance,
Samm
 
P

Peo Sjoblom

=NOW()

will give you the current time of your computer clock
if you put NOW() in A1 and format it as time

simply subtract or add the different timezones

=MOD(A1-TIME(5,,),1)

the above will give you STD us east coast time when now() is Greenwich or if
you are on the east coast and
friend is in Seattle you would get his/her time

=MOD(A1-TIME(3,,),1)

so you need to use some sort of lookup table with your friends names and
their respective +- time

so if you create a 2 column table with names in the leftmost column and
their time offset in column2 with -3 for
Seattle if your times zone is US eastern

=MOD(A1+VLOOKUP(A3,MyTable,2,0)*TIME(1,,),1)



--
Regards,

Peo Sjoblom

(No private emails please)
 
C

Chip Pearson

Which data are you providing and which data do you want to
calculate?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
S

SammyJJones

Peo,

I have tried your solution, but have made a total mess of it, I have
attached the file, could you please take a look at it and tell me where
I've gone wrong?

Thanks in advance,
Sammy.




Peo said:
=NOW()

will give you the current time of your computer clock
if you put NOW() in A1 and format it as time

simply subtract or add the different timezones

=MOD(A1-TIME(5,,),1)

the above will give you STD us east coast time when now() is Greenwich
or if
you are on the east coast and
friend is in Seattle you would get his/her time

=MOD(A1-TIME(3,,),1)

so you need to use some sort of lookup table with your friends names
and
their respective +- time

so if you create a 2 column table with names in the leftmost column
and
their time offset in column2 with -3 for
Seattle if your times zone is US eastern

=MOD(A1+VLOOKUP(A3,MyTable,2,0)*TIME(1,,),1)



--
Regards,

Peo Sjoblom

(No private emails please)


"SammyJJones" [email protected] wrote in message

Ok, you guys helped be out big time with my last question!!! So
here's
another.

I want A1 to = the current time (where I am)
B1 = Friends name
C1 = time diff in hours between us
D1 = What time it is where they are.


Is this possible???


Thanks in advance,
Sammy
Click to expand...


+-------------------------------------------------------------------+
|Filename: World Clock.zip |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=47|
+-------------------------------------------------------------------+
 
P

Peo Sjoblom

You don't need to create the lookup table if you just want the times
the way your setup is, put this in C4 and copy down

=MOD($B$1+B4*TIME(1,,),1)

also the name errors you had was because you didn't define a name for your
table, it could also have been written

=MOD($B$1+VLOOKUP(A4,$A$4:$B$8,2,0)*TIME(1,,),1)

and copied down
 
S

SammyJJones

Cheers Peo,

Worked a treat!!!

Regards,
Sammy



Peo said:
You don't need to create the lookup table if you just want the times
the way your setup is, put this in C4 and copy down

=MOD($B$1+B4*TIME(1,,),1)

also the name errors you had was because you didn't define a name for
your
table, it could also have been written

=MOD($B$1+VLOOKUP(A4,$A$4:$B$8,2,0)*TIME(1,,),1)

and copied down


--

Regards,

Peo Sjoblom
Click to expand...
 
Top