PDA

View Full Version : Find Local Time



tqm1
07-10-2007, 04:43 AM
Ref: http://www.excelforum.com/showthread.php?t=606315&highlight=slow+timer

Dear Experts

I am preparing "World Clock" project which presents running local time of every country. I am facing a problem.

I am using this formula to get local time.
=$G$15+(M6/24) ' G15 is GMT time and M6 is time difference.

If GMT Time is 04:00:00 then what will be the Local Time in Samoa (American)

The time difference between GMT and Samoa (American) is -11 hours.

How to substract 11hours from 4 hours.

While watching my workbook,Set your computer clock to 04:00:00 and see the sheet. A wrong time format is displayed against Samoa (American).

Please help

Bob Phillips
07-10-2007, 04:49 AM
=$G$15+M14/24+($G$15+M14/24<0)

Bob Phillips
07-10-2007, 04:53 AM
BTW, what about countries with multiple time zones?

tqm1
07-10-2007, 05:29 AM
=$G$15+M14/24+($G$15+M14/24<0)

Thank you very much sir, the above formula works fine.

Now another question:

How to show Date against local time

IF GMT Time is 23:00:00 then see different DATES

Country------Local Time-------Date-------Day--
England-------23:00:00-----10/07/2007---Tue--
Australia------09:00:00-----11/07/2007---Wed--

How to fill Date column against country local time?

(See attached file that has date column before every country)

Please help

Bob Phillips
07-10-2007, 05:38 AM
How will you know what the date in the UK is at any time (don't forget also that GMT is not UK local time).

tqm1
07-10-2007, 05:44 AM
How will you know what the date in the UK is at any time

Dear Sir,

I use =TODAY() in Date column against England.
and Date against this country is automatically updated with time.

If possible, Please see my attached workbook

Bob Phillips
07-10-2007, 06:02 AM
But if you are not in the UK, TODAY() might be a different day than the day in UK, and as everything is based around UK time, you need to know the day in the UK.

tqm1
07-10-2007, 06:23 AM
But if you are not in the UK, TODAY() might be a different day than the day in UK, and as everything is based around UK time, you need to know the day in the UK.

Yes it is all right, please help me in this regard.

May you still not see my workbook?

Bob Phillips
07-10-2007, 06:26 AM
I have seen the workbook, but it makes no difference.

When you pass over midnight in Pakistan, it will say Tuesday in your Excel, but it will still be Monday here in the UK for another 5 hours. It works fine for me because I am in the UK, but it won't for you, or someone who is in Australia.

And we haven't even mentioned Daylight Saving Time yet.

tqm1
07-10-2007, 06:34 AM
My dear Professor.

Should I think "This is unsolvable question of the world"?

I was told : there is a perfect solution of every problem.

May this saying is wrong?

Bob Phillips
07-10-2007, 06:46 AM
There is, but it is not trivial. The 'proper' way IMO is to connect to a website that shows these times, and extract it from there.

stanl
07-10-2007, 07:02 AM
And we haven't even mentioned Daylight Saving Time yet.

Agreed. I have attached a template that uses the TimeZones url lookups to determine offsets between 2 zones and it includes DST bias. It is set up as a simple browser display, but can be easily parsed via HTTP [homework]. Ten there is always the Iridium Work Map,

,02 Stan

mdmackillop
07-10-2007, 11:02 AM
Some reading (http://www.cpearson.com/excel/timezone.htm) for you

Bob Phillips
07-10-2007, 11:32 AM
What is Iridium Work Map Stan?

I assume it is something to do with the Iridium Satellite Network, but I can't find much more than that.

stanl
07-10-2007, 12:11 PM
What is Iridium Work Map Stan?


I meant Iridium World Map. It is something they released for free and others have built applications from it. see

http://blog.metawrap.com/blog/IridiumGlobalTimezoneConverterUtility1998BorlandC.aspx

then follow to the newer links.

Je suis idiote

tqm1
07-10-2007, 11:43 PM
Dear All,

Where to findout Timezone Map to insert it into Excel Sheet?

Thanks

stanl
07-11-2007, 03:22 AM
Dear All,

Where to findout Timezone Map to insert it into Excel Sheet?

Thanks

here is one (there are hundreds more]

http://www.worldservicegroup.com/images/tzmap.jpg

are you looking for something to insert as an image or set as a background? Stan

stanl
07-11-2007, 06:41 AM
you might also find the attached interesting. it will calculate times based on the time of your PC. The trick would be to keep the browser invisible, or use HTTP and just crop off the generated map and paste it into your workbook. I'm not quite even with the second part yet:banghead: Stan