Show current time (EST)

Question

I created a UserForm that contains a textbox that will show the current time in EST. The only problem is, the time that's reflecting is of course the current time in our country so I want to convert it in EST, which is - 12:00 from our time.

Private Sub UserForm_Initialize()
If ActiveWorkbook.MultiUserEditing Then

End If

Application.ScreenUpdating = False
Application.DisplayAlerts = False
    txtDate.Value = Format(Now, "mm/dd/yyyy")


Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

Answer 1

Its far simpler if you want to use it in a formula. Use the NOW() function, and then subtract the difference using TIME function.

For example, I am in a timezone which is 9:30 hrs ahead of EST. So the get EST time, I'll take the current date-time in my timezone using NOW(), and then subtract 9 hrs 30 minutes from it using the following formula.

=NOW()-TIME(9,30,0)

Enjoy!

Answer 2

You need to use TimeValue function like this :

txtDate.Value = Format(Now - TimeValue("12:00:00"), "mm/dd/yyyy")

Answer 3

A more complicated way than R3uK but I've always preferred using TimeSerial() and DateSerial() to get my exact criteria setup (especially when you need to modify multiple parts of the Time).

Sub TimeToEST()

Dim ESTHour As Integer
If Hour(Now) - 12 < 0 Then
    ESTHour = 24 + Hour(Now) - 12
Else
    ESTHour = Hour(Now) - 12
End If

txtDate.Value = TimeSerial(ESTHour, Minute(Now), Second(Now))

End Sub

To show just the current date in EST:

Sub DateToEST()

Dim ESTDate As Date
If Hour(Now) < 12 And Day(Now) = 1 Then
    ESTDate = Now - TimeValue("12:00:00")
ElseIf Hour(Now) < 12 Then
    ESTDate = DateSerial(Year(Now), Month(Now), Day(Now) - 1)
Else
    ESTDate = DateSerial(Year(Now), Month(Now), Day(Now))
End If

txtDate.Value = ESTDate

End Sub