Time entry - Zulu time

trevolly

Board Regular
Joined
Aug 22, 2021
Messages
66
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

trevolly

Board Regular
Joined
Aug 22, 2021
Messages
66
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
14,631
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
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.
VBA 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 = Local2GMT(Now())
End If
End Sub
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,568
Messages
5,770,900
Members
425,651
Latest member
Mark Cashin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top