Standart time source ?

Sinbad

Board Regular
Joined
Apr 18, 2012
Messages
224
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.
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,884
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
You could use a different cell as below
Sheet1

*AB
108/07/2012 21:2409/07/2012 01:24

<tbody>
</tbody>

Spreadsheet Formulas
CellFormula
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

*AB
108/07/2012 21:2809/07/2012 01:28
2**
309/07/2012 01:28*

<tbody>
</tbody>

Spreadsheet Formulas
CellFormula
A1=NOW()
B1=A1+"04:00"
A3=NOW()+"4:00"

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 
Last edited:

Sinbad

Board Regular
Joined
Apr 18, 2012
Messages
224
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.
 

Sinbad

Board Regular
Joined
Apr 18, 2012
Messages
224

ADVERTISEMENT

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 :rolleyes: something to that effect anyways..
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,884
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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

Board Regular
Joined
Apr 18, 2012
Messages
224
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,040
Messages
5,599,456
Members
414,312
Latest member
mikefire911

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top