Assigning real time to a cell in 2007?

Good morning Gurus! I want to thank everyone in advance. This site has been so helpful in developing tools to assist with projects, productivity, and checklists. Thank you all.

On to my new "problem"....

As with all other inquiries I have made, this is for a non VBA solution, as VBA is prohibited at work.

I would like to assign a cell to represent the current time. I am not referring to a "timestamp"; but an actual running clock if the worksheet is open. Ideally, I would like to have several cells, each representing a different time zone.

Then I would like to set up a rule for column F (where I have the State that each of my accounts is located in). This rule would show the current time in that state in the adjacent cell (i.e. column G). Something along the lines of if column G is MD, then column F shows current time eastern.

I am in a call center, and this would be helpful to associates making outbounds so they do not call at the wrong time, and everyone is always losing their time zone cheat sheet.

Thanks again.

Can't see that happening without VBA.

The only way i could see doing it without VBA is to have a situation where before they make the outbound call they enter their local time into a cell say B4. then you can use a formula in the state cells to work off of cell B4 to adjust off your local time. But like Bob said , can't see this being automated without VBA.

Hi there,

Current date and time for your desk can be acheived with the =NOW() formula

From there, you should be able to work out the other time zones by adding the number of hours different divided by 24. So:

=A1+1/24
=A1-3/24

The times will all recalculate each time the workbook is recalculated (activated or press F9 to force it)

HTH,

If you are allowed ... you could do a Web Query off of time.gov to pull in your local time ( adjust properties to refresh every 1 minute). then have the state cell formuals adjust time off that.

Tommy, I love that idea.

Tried to go set up a qebquery and it asked me to install an ActiveX Control/java applet.


Nice! I'm sure that there is a way to get around this, but I'm guesing if VBA is forbidden in the OP's organization, installing ActiveX controls may require admin priviledges as well.

Gotta love security's ability to kill off useful features.

Heya Ken,

just for curiosities sake ... try this . goto following link http://time.gov/timezone.cgi?Central/d/-6/java

look at the top right corner and you will see a link to Disable Java (click that) it will now bring up another page with java disabled.

Now run your Web Query off of that URL. Just curious to see if that was what caused your query to error out like that.

My thoughts were pull the Web Query down into sheet2 A1 Time will be in cell A2 ( the query pulls in extra data not required that is why it needs to be on a seperate sheet ) you could then use the state formulas like you posted against A2 in Sheet2.

Aha! Yes, that works. (Didn't see that option!)

Looks like the time returned is just time though (for Jan 1, 1900), so you'd need to add "Today()" to it in order to get the correct date/time as the clock passes over midnight in each time zone.