question of time

In my checklists I use a simple checkbox that inserts the Date and Time [using PC time] and the user name [from the users PC profile]

See attached file.

Is it possible to change this so that it will take the current time from the internet rather than from the PC

Reason for this is when a user is using this locally in their own country, but where their PC is set to use UK Time the details on the checklist are incorrect.

i.e. UK time 14:17  US time 09:17

UK time is 14:17 Hong Kong time 21:17

Hope I have explained this ok

thanks in advance
What_Time.xlsm
JagwarmanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rob HensonFinance AnalystCommented:
Find a website that shows the required time and you might be able to connect as a data source.
0
Martin LissOlder than dirtCommented:
You could create a table of countries and there time offset from UK time and add the difference.
0
als315Commented:
You can use script in your code and get time zone from registry:
Sub AuditDetailsDaily()
    Dim cb As CheckBox
   Set cb = ActiveSheet.CheckBoxes(Application.Caller)
    With cb
        
        With .TopLeftCell
            If cb.Value = msoCTrue Then
                .Offset(0, 1).Value = DateAdd("h", Tzone(), Now())
                .Offset(0, 2).Value = Environ("username")
            Else
                .Offset(0, 1).Resize(, 2).ClearContents
            End If
        End With
        
    End With
    
    
End Sub

Function Tzone() As Integer
  Dim rval As String
  Dim WsObj As Object
  rval = "HKLM\system\currentcontrolset\control\timezoneinformation\"
  Set WsObj = CreateObject("wscript.shell")
  Tzone = WsObj.regread(rval & "bias") \ 60
  Set WsObj = Nothing
End Function

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Martin LissOlder than dirtCommented:
@als315: Nice code!
0
David Johnson, CD, MVPOwnerCommented:
The US has 6 primary time zones plus Indiana (their DST is different than the rest of Eastern Time including Hawaii and Samoa. Canada has 6 TZones, Russia has 6, China has only 1 .. Your checklist should use UTC to make things consistent.. if you are using a date/time object then this is already done for you just the displayed time will be in the users culture settings
0
[ fanpages ]IT Services ConsultantCommented:
Jagwarman:

Can you use what has been provided above?

I have just amended a routine to query Network Time Protocol [NTP] servers to retrieve a time adjusted to a PC's local time zone; with, or without, an offset for Coordinated Universal Time [UTC... yeah! I know :)].

The primary server is one hosted by Microsoft in Redmond, Washington,

If you are in the UK, I can source a "better" (read: closer) list of servers for the routine to query.  Presently I have taken a list of fourteen US-based servers (based on some code previously written).

However, if you can let me know which countries will be using the code, I can look at a concise list so that every user has the fastest response possible from the routine.

There is an argument that each user regardless of their country of origin all should use one server, or a set of servers in a predefined order, to avoid any possible deviation from one fixed time, but I will leave this to you to determine how to progress.

PS. Some background reading (if you have trouble sleeping)...

[ http://www.ntp.org ]
[ http://www.pool.ntp.org/en ]
[ http://tf.nist.gov/tf-cgi/servers.cgi ]
[ https://en.wikipedia.org/wiki/Network_Time_Protocol ]

PPS. Credit to Martin L Gore/Depeche Mode for the title of this thread! :)
0
JagwarmanAuthor Commented:
als315,

when you say "You can use script in your code and get time zone from registry:"

are you saying its not just a case of using the code you have here.

I put that code into my checklist but it still came out as London Time
0
[ fanpages ]IT Services ConsultantCommented:
^ Change the timezone/region on your PC to elsewhere in the world, re-run the code above, & note the difference.
0
JagwarmanAuthor Commented:
fanpages.

we are not able to change the timezone/region on our PCs
0
[ fanpages ]IT Services ConsultantCommented:
OK, can you ask any of your colleagues in other regions to execute als315's code (above)?

If not, how are you going to be able to test any suggestions posted here (mine pending, still) meet your requirements?
0
JagwarmanAuthor Commented:
did that and it still showed UK time
0
[ fanpages ]IT Services ConsultantCommented:
...and what region/timezone/International settings were being used on that test machine?
0
JagwarmanAuthor Commented:
they are set to their local time 21:39
0
[ fanpages ]IT Services ConsultantCommented:
So, although you did not answer my question, I think the code above does not work as intended, as when it is executed it shows a time 7 hours behind the actual time in the region.

Is that correct?
0
JagwarmanAuthor Commented:
Sorry... yes
0
als315Commented:
Code above get time shift form registry, nothing more/ There are no information about summer time (DayLight). You can play with other parameters from this path of registry:
ActiveTimeBias
Tzone = WsObj.regread(rval & "ActiveTimebias") \ 60

In this case you will have real UTC+0 time (without daylight savings) and it will be same for all time zones
0
[ fanpages ]IT Services ConsultantCommented:
Do you need any further help here, Jagwarman?

If so, please may I refer you to my previous comment, ID: 40967427?
0
JagwarmanAuthor Commented:
als315.... sorry for delay was caught up on something else.

Fanpages. thanks for your input but I was able to make use of the solution from als315
0
[ fanpages ]IT Services ConsultantCommented:
Why have you accepted a comment that you confirmed (in ID: 40968987/ID: 40969026/ID: 40969082) did not answer your question?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.