Assigning real time to a cell in 2007?

bigbluesfan22

New member
Joined
Jul 27, 2012
Messages
3
Reaction score
0
Points
0
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.
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
Can't see that happening without VBA.
 

tommyt61

New member
Joined
Mar 26, 2011
Messages
153
Reaction score
0
Points
0
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.
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,524
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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,
 

tommyt61

New member
Joined
Mar 26, 2011
Messages
153
Reaction score
0
Points
0
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.
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,524
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Tommy, I love that idea.

Tried to go set up a qebquery and it asked me to install an ActiveX Control/java applet.
8-27-2012 9-17-46 AM.png

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.
 

tommyt61

New member
Joined
Mar 26, 2011
Messages
153
Reaction score
0
Points
0
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.
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,524
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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.
 
Top