Can I put the Actual Date in Excel or VBA?

[Flaco]

Sponsored Links

I want to get an actual current date in Excel or VBA. I know that the methods like today() or now() is useful for getting the current date and time. This methods displays the current date and time but of system clock. Does anyone have some code that I can use to somehow get the correct date, irrespective of what is set as the system date in Windows?? I guess that you got what I want to ask..!? Please help me.!!

[Deabelos]

I am doing many R & D from last year for doing the same thing but not succeeded yet. All methods and the date functions will give you the timing that your system is showing to you. There are many date functions but that all will refer to your system date for showing the current date and time. If your system date is wrong then the function will return the same date which is wrong. So I don't think that there is some code that will predict the correct date and then will display to us.

[void]

You can put the Actual Date in Excel or VBA by using the World Time Zone Database Subscription Service. The printed edition of the Time Zone Guide provides a comprehensive list of over 495 time locations ordered by their political names as shown on World Time Server.com. The plus point is that complete database in XML format and ASCII comma delimited format for easy import or direct use in any custom project development you create. The only and big drawback is that their subscription fee is $399.95 (US) per year. So I don't think that anyone will be interested for this service.

[Flaco]

Thanks Friends for trying to solve my problem. I am shocked to hear that World Time Zone Database Subscription Service provides the service with the subscription fee of $399.95. I think that I will find some another way or I will use the same time that my system uses. I am not doing any important project neither I am creating any big application, so I will rely on my system time or date using the common functions like today() or now().

[Warner]

If you are connected to the Internet then you can use the following coding for putting the actual date in Excel or VBA.

Private Sub Command1_Click() 'Main button to set the system
    ' time
    On Error GoTo ErrHandler

    Label3.Caption = "System Time has Not been Set Yet"

    SetIt = 1 'Used to only set time if the time from the
        ' time server is valid and reportedly accurate

    If Winsock1.State <> sckClosing Then 'Sometimes the
        ' Winsock gets delayed in the closing state, so
        ' make sure it is closed before trying again
       If Winsock1.State = sckClosed Then 'If closed, ok to
           ' open, else close it
          Timer1.Interval = 5000 'Start 5 second count to
              ' 'time' server
          Timer1.Enabled = True
          Screen.MousePointer = vbHourglass
          Winsock1.LocalPort = 0 'Must be set to 0
          Winsock1.RemoteHost = Trim$(Text1.Text) 'Address
              ' of NIST server
          Winsock1.RemotePort = 13 '13, 37 or 123 'Use 13!
          Winsock1.Protocol = 0 '1-UDP '0-TCP 'USE TCP!
          Winsock1.Connect 'This is what goes out and gets
              ' the time
       Else
          Winsock1.Close
          Screen.MousePointer = vbNormal
          Timer1.Interval = 0
          Timer1.Enabled = False
       End If
    Else
       Winsock1.Close
       Screen.MousePointer = vbNormal
       Timer1.Interval = 0
       Timer1.Enabled = False
    End If
    
    Exit Sub
ErrHandler:
    SetIt = 0
    Screen.MousePointer = vbNormal
    Timer1.Interval = 0
    Timer1.Enabled = False
    MsgBox "The Winsock Connection is Unavailable."
    Winsock1.Close
End Sub

The above code uses a Winsock control to connect to a NIST time server. By using this code your system time will keep on updating. And then using the simple functions will give output of actual date.

[opaper]

Even I think that you should try for the NIST. The NIST Internet Time Service (ITS) allows users to synchronize computer clocks via the Internet. The service responds to time requests from any Internet client in several formats including the DAYTIME, TIME. The Network Time Protocol (NTP) is the most commonly used Internet time protocol, and the one that provides the best performance. Most of the NIST time servers do not require any authentication when requesting the time in NTP format, and no keys or passwords are needed to use this service.