Macro converts time entered in column E and F from Indian time to CST( without daylight saving)

[Abhijeet Gudur]

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo Error_out:
Dim mrange As Range
Set mrange = Range("E:F")
If Not Intersect(Target, mrange) Is Nothing Then
Target = Target - TimeValue("13:30")
End If
Application.EnableEvents = True
'MsgBox ("Check Day-Light-Saving") optional
Exit Sub
Error_out:
Application.EnableEvents = True
End Sub

 

[Abhijeet Gudur]

Below code enters system's(India) time in columns N or T, and converts it into CST. assign this to some shortcut key and users can run it directly. Like ctrl+shift+E.

Option Explicit
Sub Time_in_CST()
Dim varT As Date
Application.EnableEvents = False
On Error GoTo Error_out:
Dim mrange As Range
Set mrange = Range("N:T")
If Not Intersect(ActiveCell, mrange) Is Nothing Then
ActiveCell.FormulaR1C1 = "=NOW()"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
varT = Selection.Value
ActiveCell.Value = varT - (11.5 / 24)
Application.CutCopyMode = False
End If
Application.EnableEvents = True
Exit Sub
Error_out:
Application.EnableEvents = True
End Sub