Standart time source ?

[Sinbad]

hi, is there a formula like =now() that will get the date and time from a know time source?

Reason is simple. Different people access a shared spreadsheet from different timezones and the =now() returns the user specific time & date from their system.

If we could have an external known time source we could add a few hour to move the time into any given timezone we like. ie we like working in UTC, so if the timezone is ACT we could just calculate it and convert it to UTC, but with =now() this seems impossible.

Thank you.

 

[MARK858]

You could use a different cell as below
Sheet1

*	A	B
1	08/07/2012 21:24	09/07/2012 01:24

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula A1 =NOW() B1 =A1+"04:00" <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

or even the same cell
Sheet1

*	A	B
1	08/07/2012 21:28	09/07/2012 01:28
2	*	*
3	09/07/2012 01:28	*

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula A1 =NOW() B1 =A1+"04:00" A3 =NOW()+"4:00" <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Sinbad]

indeed.. BUT....

If Mike from the Dom Rep opens the spreadsheet he will have his time in there... lets say 10/04/2012 10:00:00 (GMT -4)

Now Paula opens it just a few seconds later, she is in India and the cell will change to 11/04/2012 20:00:00 (GMT +5)

Hence we need a "known" time that does not change, best would be a time already in UTC, but if it would be elsewhere we could just "make" it utc by using a formula.

 

[MARK858]

Have a look at Tusha's site and see if that helps

http://www.tushar-mehta.com/excel/timezone.htm

 

[Sinbad]

very interesting solution, but does not help. he uses local time to calculate different timezones.

This is exactly our problem. we need to get away from local times and hence the question if there is a way to query some external clock source.. some weird and wonderful atomic clock as they call them i believe.. =get_time_from_atomic_clock(Harvard) would be fun something to that effect anyways..

 

[MARK858]

Would be a nice idea but I think they transmit in regional time. The only way I can think of at the moment that is close is Chip Pearson's API that looks at the local system time on the PC and converts to UTC/GMT.

I haven't tried it (not that brave).
I don't think it will help your situation but just in case the link is below.

http://www.cpearson.com/excel/TimeZoneAndDaylightTime.aspx

 

[Sinbad]

I think I'll join you on the bench... I like his warning.

Lets see if anybody else has another "safer" solution maybe.

Thanks though for the interesting feedback.