How to Convert Time to UTC?

[elitef]

Hi Everyone,

Hopefully this is simple to do, but figured I'd ask...
Is there a way to convert a time listed in A1 (ex. 01:00PM) to UTC in A2?

I am fine with either formula or VBA, whichever will accomplish this.

I've already added the GimmeUTC function to the sheet, but I am not sure how to use the function to actually convert a time thats listed.
I know you can call GimmeUTC() but that will give a current timestamp, just like Now() but in UTC, but this is not what im looking for, as I am looking to actually convert the times listed in A1 of the users current timezone from their time/regional OS settings.

This is a sheet which is used by multiple teams across the world, so I am trying to fiddle around and see if this will be possible or not.

Any help is greatly appreciated

 

[PeteWright]

This seems a little bit tricky or even impossible, since you don't know which time zone was entered in A1. It could be anything.

Please correct me if I got you wrong.

 

[NoSparks]

Maybe have a look at the Local Time to UTC function here

 

[elitef]

This seems a little bit tricky or even impossible, since you don't know which time zone was entered in A1. It could be anything.

Please correct me if I got you wrong.

Yes, that is correct, so I was hoping to find a way to do so by having VBA look at what the Windows OS regional / time settings are set to for timezone

 

[Logit]

Option Explicit

Function UTCNow() As Date
Dim dt As Object
Set dt = CreateObject("WbemScripting.SWbemDateTime")
dt.SetVarDate Now
UTCNow = dt.GetVarDate(False)
End Function

Sub Demo()
Dim localTime As Date, utcTime As Date
Dim dateTimeOffset As String, Unary As String
Dim timeDiff As Date
localTime = Now
utcTime = UTCNow()
timeDiff = localTime - utcTime
Unary = IIf(timeDiff < 0, "-", "")
dateTimeOffset = Format(localTime, "yyyy-mm-dd hh:mm:ss") & " " & Unary & Format(timeDiff, "hh:mm")
'Debug.Print dateTimeOffset
MsgBox dateTimeOffset
End Sub

Here is another approach :

 

[PeteWright]

@Logit
Maybe you can determine the users time offset, but you still don't know which time zone was entered in cell A1, right?

@elitef
Is there any other information provided besides just the time?
How do you work with the workbook? I mean, where does the time in cell A1 come from?

 

[elitef]

Option Explicit

Function UTCNow() As Date
Dim dt As Object
Set dt = CreateObject("WbemScripting.SWbemDateTime")
dt.SetVarDate Now
UTCNow = dt.GetVarDate(False)
End Function

Sub Demo()
Dim localTime As Date, utcTime As Date
Dim dateTimeOffset As String, Unary As String
Dim timeDiff As Date
localTime = Now
utcTime = UTCNow()
timeDiff = localTime - utcTime
Unary = IIf(timeDiff < 0, "-", "")
dateTimeOffset = Format(localTime, "yyyy-mm-dd hh:mm:ss") & " " & Unary & Format(timeDiff, "hh:mm")
'Debug.Print dateTimeOffset
MsgBox dateTimeOffset
End Sub

Here is another approach :

Thank you for this, but this only gives me a msgbox that displays the current time and the difference between my timezone, EST, and UTC, but how would I be able to use that detail to populate A2 with the converted time into UTC?

Also, not seeing the "Another approach:" code

 

[elitef]

@Logit
Maybe you can determine the users time offset, but you still don't know which time zone was entered in cell A1, right?

@elitef
Is there any other information provided besides just the time?
How do you work with the workbook? I mean, where does the time in cell A1 come from?

There is a Date field and a Time field only which would be used for this conversion. Unfortunately no timezone. I'd prefer to find a way to avoid manual entry of a timezone to avoid having the user forget to select it and it will then not convert the time correctly, etc.

Thats why if there was a way to get the users timezone based on their OS, that would be a bit better

 

[elitef]

@Logit
Maybe you can determine the users time offset, but you still don't know which time zone was entered in cell A1, right?

@elitef
Is there any other information provided besides just the time?
How do you work with the workbook? I mean, where does the time in cell A1 come from?

I did try @Logit 's recommendation and it did work to find what their OS regional setting is. I tried adjusting mine to India and Bulgaria as examples, and I reran the sub and it did spit out the time difference between LOCAL and UTC. So thank you for that, @Logit

So, we can do it like this:
A1 = November 26, 2024 (this will be manually inputted by the user)
B1 = 12:00 PM (this will be manually inputted by the user)
C1 = -05:00 (this will be populated by @Logit 's code

Now to figure out a way to convert the above A1:C1 to convert it to UTC in A2:C2

Any help would be appreciated

 

[Logit]

It is my understanding that Windows has an internal setting that knows the UTC Time Zone for any computer. That is whether it is located in the US, Europe, SE Asia, etc.

The following is supposed to tell you the current UTC for any computer :

Sub UTCTime()

Dim dt As Object, utc As Date
Set dt = CreateObject("WbemScripting.SWbemDateTime")
dt.SetVarDate Now
utc = dt.GetVarDate(False)
MsgBox utc
End Sub

I don't understand what you mean by converting certain cells to UTC ???