Results 1 to 8 of 8

Thread: Assigning real time to a cell in 2007?

  1. #1

    Assigning real time to a cell in 2007?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

  2. #2
    Can't see that happening without VBA.

  3. #3
    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.

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,208
    Articles
    57
    Blog Entries
    14
    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,
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    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.

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,208
    Articles
    57
    Blog Entries
    14

    Unhappy

    Tommy, I love that idea.

    Tried to go set up a qebquery and it asked me to install an ActiveX Control/java applet.
    Click image for larger version. 

Name:	8-27-2012 9-17-46 AM.png 
Views:	11 
Size:	24.6 KB 
ID:	750

    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.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  7. #7
    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.

  8. #8
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,208
    Articles
    57
    Blog Entries
    14
    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.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •