world clock in excel

[thecueball]

Guys n' gals,

Is there a way to turn excel into a world clock?????

I am working for a company that has offices around the world, and I would like to see the time in all offices, when I am working in excel without using the internet (or spending monay on loads of wall clocks!!)

TIA

CB

 

[SteveO59L]

Define your time zones and add or subtract house from NOW() as necessary

 

[thecueball]

Steve,

Easy for you to say!!!!!!

thanks for the reply, but :

a) Where do I define the timezones??? I know what they are ie GMT -8 GMT+1, etc but wher would I put them in?

b) I have tried to subtract values from the =now() formula, and can only get errors!

cheers

CB

 

[SteveO59L]

In a cell (say H5) enter Now() and format as HH:MM.

In J5 (formatted as HH:MM) the formula =H5+TIME(2,0,0)


will return the time two hours from now.

 

[thecueball]

Superb!!!

thanks for your help Steve

 

[gordyzx9r]

In a cell (say H5) enter Now() and format as HH:MM.

In J5 (formatted as HH:MM) the formula =H5+TIME(2,0,0)


will return the time two hours from now.

What if it is supposed to be an earlier time, how do you put in a -GMT?

 

[Joe Was]

=$H$5+TIME(2,0,0) is now + 2hrs.
=$H$5-TIME(2,0,0) is now - 2hrs.


I an sure you entered something like =$H$5+TIME(-2,0,0) and got a Num error?

When you get something wrong you need to experiment, untill you get the right combination it is the best way to learn. Just because the first thing you try, does not work, you should not give up, if you do you will never learn.

 

[gordyzx9r]

I an sure you entered something like =$H$5+TIME(-2,0,0) and got a Num error?

The error I received is the Negative Dates or Times Are Displayed as #####, when the time in H5 is 1 AM (01:00) and =$H$5+TIME(-2,0,0) is used.

 

[Joe Was]

"######" indicates that it does have an answer but your column width is too small to show it all!
Just make your Column width larger and it will show.

As I indicated above you cannot use negative time!

You must add or subtract positive time only!

=$H$5+TIME(2,0,0) is now + 2hrs.
=$H$5-TIME(2,0,0) is now - 2hrs.

 

[gordyzx9r]

"######" indicates that it does have an answer but your column width is too small to show it all!

That's what I thought too...but I made the field width 70.00 and it's still not showing the time. And I rechecked the format, it's CUSTOM HH:MM (and for grins I went in goofed with all the TIME formats as well to try and see if that would work).

As I indicated above you cannot use negative time!

You must add or subtract positive time only!

=$H$5+TIME(2,0,0) is now + 2hrs.
=$H$5-TIME(2,0,0) is now - 2hrs.

And that's exactly how I have your formula in there too.

=$H$5-TIME(2,0,0)

However, if I enter 00:00 (12 AM) or 01:00 (1 AM)...it gives me ###### (and it doesn't matter how big I make the cell), any other time will work just fine. I enter 23:00, I get 21:00...I enter 01:00 and I get #'s.