Solved

Entering Greenwich time into a cell directly, possibly from some web site

Posted on 2014-03-25
8
150 Views
Last Modified: 2014-03-26
Hello,
I searched the MS Excel site; http://office.microsoft.com/en-us/excel-help/excel-functions-by-category-HP005204211.aspx for a function that would give me the current Greenwich time, which I could use in a cell.
I found this =A2+1-TIME(6,0,0), which I could work with, but I am hoping for some function like; =TIME(Greenwich).  Then I could convert this time to any other time zone.
Comments?
0
Comment
Question by:chima
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
8 Comments
 
LVL 39

Expert Comment

by:nutsch
ID: 39954492
You'll get the full lowdown on it on Chip Pearson's page: http://www.cpearson.com/excel/LocalAndGMTTimes.htm

Unless you're ready to go with Windows API and user-defined functions, there is no straight solution for you, but you could set defined names to accomplish something similar, e.g. set Greenwich as a defined name =6/24, assuming you're six hours west from GMT, and then you can use it in a formula like

=now()+Greenwich
0
 
LVL 27

Assisted Solution

by:MacroShadow
MacroShadow earned 150 total points
ID: 39954515
Try this from http://www.myengineeringworld.net/2013/09/retrieve-time-from-internet-server-vba.html
Option Explicit

Function InternetTime(Optional GMTDifference As Integer) As Date

    '-----------------------------------------------------------------------------------
    'This function returns the Greenwich Mean Time retrieved from an internet server.
    'You can use the optional argument GMTDifference in order to add (or subtract)
    'an hour from the GMT time. For Example if you call the function as:
    '=InternetTIme(2) it will return the (local) hour GMT + 2. Note that the
    'GMTDifference variable is an integer number.
   
    'Written by:    Christos Samaras
    'Date:          25/09/2013
    'Last Updated:  20/11/2013
    'e-mail:        xristos.samaras@gmail.com
    'site:          http://www.myengineeringworld.net
    '-------------------------------------------------------------------------------

    'Declaring the necessary variables.
    Dim Request     As Object
    Dim ServerURL   As String
    Dim Results     As String
    Dim NetDate     As String
    Dim NetTime     As Date
    Dim LocalDate   As Date
    Dim LocalTime   As Date
   
    'Check if the time difference is within the accepted range.
    If GMTDifference < -12 Or GMTDifference > 14 Then
        Exit Function
    End If

    'The server address.
    ServerURL = "http://www.timeanddate.com/worldclock/fullscreen.html?n=2"
   
    'Build the XMLHTTP object and check if was created successfully.
    On Error Resume Next
    Set Request = CreateObject("Microsoft.XMLHTTP")
    If Err.Number <> 0 Then
        Exit Function
    End If
    On Error GoTo 0
   
    'Create the request.
    Request.Open "GET", ServerURL, False, "", ""
   
    'Send the request to the internet server.
    Request.Send
   
    'Based on the status node result, proceed accordingly.
    If Request.ReadyState = 4 Then
       
        'If the request succeed, the following line will return
        'something like this: Mon, 30 Sep 2013 18:33:23 GMT.
        Results = Request.getResponseHeader("date")
       
        'Use the Mid function to get something like: 30 Sep 2013 18:33:23.
        Results = Mid(Results, 6, Len(Results) - 9)
       
        'Use the Left and Right function to distinguish the date and time.
        NetDate = Left(Results, Len(Results) - 9) '30 Sep 2013
        NetTime = Right(Results, 8) '18:33:23
       
        'Convert the date into a valid Excel date 30 Sep 2013 -> 30/9/2013.
        'Required for countries that have some non-Latin characters at their alphabet (Greece, Russia, Serbia etc.).
        LocalDate = ConvertDate(NetDate)

        'Add the hour difference to the retrieved GMT time.
        LocalTime = NetTime + GMTDifference / 24

        'Return the local date and time.
        InternetTime = LocalDate + LocalTime
   
    End If
   
    'Release the XMLHTTP object.
    Set Request = Nothing

End Function

Function ConvertDate(strDate As String) As Date

    '-------------------------------------------------------------------------
    'This function converts the input date into a valid Excel date.
    'For example the 30 Sep 2013 becomes 30/9/2013.
    'Required for countries that have non-Latin characters at their alphabet.

    'Written by:    Christos Samaras
    'Date:          25/09/2013
    'e-mail:        xristos.samaras@gmail.com
    'site:          http://www.myengineeringworld.net
    '-------------------------------------------------------------------------

    'Declaring the necessary variables.
    Dim MyMonth As Integer

    'Check the month and convert it to number.
    Select Case UCase(Mid(strDate, 4, 3))
        Case "JAN": MyMonth = 1
        Case "FEB": MyMonth = 2
        Case "MAR": MyMonth = 3
        Case "APR": MyMonth = 4
        Case "MAY": MyMonth = 5
        Case "JUN": MyMonth = 6
        Case "JUL": MyMonth = 7
        Case "AUG": MyMonth = 8
        Case "SEP": MyMonth = 9
        Case "OCT": MyMonth = 10
        Case "NOV": MyMonth = 11
        Case "DEC": MyMonth = 12
    End Select

    'Rebuild the date.
    ConvertDate = DateValue(Left(strDate, 2) & "/" & MyMonth & "/" & Right(strDate, 4))

End Function

Open in new window


The InternetTime function will return GMT.
0
 

Author Comment

by:chima
ID: 39954558
nutsch, thanks, that work for me.  Now how could I "refresh" the excel sheet?  Is there a keyboard key like F5 to refresh the functions used, like =now() ?
I set the cell format to hh:mm:ss and used =now()+TIME(6,0,0) to convert it to Greenwich time.
Now I need a way to easily refresh the formula, to update it.
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

Author Comment

by:chima
ID: 39954561
MicroShadow, I'll keep it in mind, thanks
0
 

Author Comment

by:chima
ID: 39954590
nutsch, F9 seems to do the trick.
0
 
LVL 39

Accepted Solution

by:
nutsch earned 350 total points
ID: 39954610
Indeed, F9 is the Calculate Now shortcut.
0
 

Author Closing Comment

by:chima
ID: 39956852
macroShadow, your answer was great.  I needed a simple solution.  Thank you both.
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39957148
No problem, we're here for you're benefit...
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question