Time entry - Zulu time

[trevolly]

Morning all,

I currently have some vba code running which when a staff member double clicks in a specific range of cells it enters the current time - 1 hour due to the UK currently being in Zulu time. This is the code.....

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Date stamp with zulu
If Not Intersect(Target, Range("B4:B249")) Is Nothing Then
Cancel = True
Target.Value = Now() - (1 / 24)
End If
End Sub

Is there anything I can add to this code that will revert the double click back to "normal time" (not -1 hour) when the clocks hit 02:00am on the 31/10 ? We work 24 hours and I'm not there to delete the -(1 / 24) part of the command.

Thanks all

T

 

[jasonb75]

See if this helps

Get standard GMT time from the system using vba.

Hi, Is there any method by which we can get GMT time from any system. I tried NOW() but it gives the current system time according to the system's Time Zone. Thanks in Advance.

excelfox.com

 

[trevolly]

Thanks for replying @jasonb75 - I looked at the link but its a bit above my excel vba experience. I'll just have to tell my staff not to use the time stamp double click after 2am!

 

[jasonb75]

I looked at the link but its a bit above my excel vba experience.

You just need to copy and paste the code from post 2 of the link into a standard module (in the vba editor menu Insert > Module). You may get one line that shows up as an error (red text in the editor), in which case, if you replace that line with the one below then it should work fine.
@Rick Rothstein would you be able to confirm if this is correct, the code that I have linked to in post 2 is yours from a different forum. It appears to work correctly but it's alien code to me.

Private Declare PtrSafe Function GetTimeZoneInformation Lib "kernel32" _
               (lpTimeZoneInformation As TIME_ZONE_INFORMATION) As Long

Finally, a small change to your existing code as below to call the function that you have added above.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    'Date stamp with zulu
If Not Intersect(Target, Range("B4:B249")) Is Nothing Then
    Cancel = True
    Target.Value = Local2GMT(Now())
End If
End Sub