Help with World Clock

**yus786** · 02-16-2012, 04:50 PM

Hey, i'm having problems with the attached.

I have been asked to create a sheet in excel which looks at the world clock. So for instance if i enter 14.00 and select say India - it will give me the time in India

For the GMT+ it works OK, but for those with -GMT like USA etc, all i get is ####### sign.

Could someone please have a look at it and let me know where i've gone wrong?

Ideally i would like the current pc date to determine whether its BST or not but hey...

Thanks

**knewbry** · 02-16-2012, 08:37 PM

The problem isn't that all your negative GMT times are returning #####. It's only when the negative GMT amount is greater than the amount time you entered into cell C2 as your comparison time. So for instance if you put in 5:00 (5:00 AM), and the GMT for the area you select is -4, it will return 1:00 just fine. However, because you don't have a date, only a time, if the negative GMT amount that you're subracting from the comparison time is greater than the comparison time, then the time value (which is a decimal number in excel) becomes a negative number and that's where your problem comes in.

Since you're not concerned with a date, only with the time, the easiest way to fix it would be to add 24 hours to all your negative GMT values. If American Samoa is -11 GMT, it's also +13 GMT. That will calculate your times just fine. It would throw the date off by one day if you did it that way, but you're not using dates in your worksheet.

**Marcol** · 02-17-2012, 05:26 AM

I don't understand your Lookup Table. Why are the time differences either an integer, or an integer + 0.5?
If you change the list to integer values then this will work in B6

=IF(B2="N",1+(LOOKUP(A2,Country,GMT)/24)+C2,1+(LOOKUP(A2,Country,GMT)/24)+C2-(1/24))

Note
1/. No need for Column BST
2/. Your data validation in C2 needs to be Time > Greater or equal to > 00:00:00
3/. I have made your lookup table dynamic.

[EDIT]

I don't understand your Lookup Table .....

Okay, my mistake, leave the time differences as they are. i.e. as decimal numbers.
The formula still works

**snb** · 02-17-2012, 06:00 AM

or

PHP Code:


=NOW()+IF(VLOOKUP(A2;J:K;2)<0;-TIME(ABS(VLOOKUP(A2;J:K;2));0;0);TIME(ABS(VLOOKUP(A2;J:K;2));0;0))

or

PHP Code:


=NOW()+(2*(VLOOKUP(A2;J:K;2)>0) -1)*TIME(ABS(VLOOKUP(A2;J:K;2));0;0)

**Marcol** · 02-17-2012, 06:28 AM

If you put =Now() in C2 you'll get the current times, this will not automatically update unless you make changes to, or recalculate, the sheet.
You can enter a time or the above formula in C2, the validation will accept either

[EDIT]
This will be a bit more complicated when you start considering local daylight saving times.
Dates will come into play.
Watch out for other calendars when dates come into play. e.g. Islamic, Hindu etc.