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

 

[felixstraube]

Just to understand the whole process.

1.
The file you are working on, will it be the same file which will be accessed by every team of every location? For example the file will be stored in the cloud (one drive, SharePoint or something similar) and everybody will actually be accessing the same file?
Or will everybody receive a copy of the file?

2. Do every team input some datetimes (in their timezone) and these datetimes should be read correctly by the other teams (in other timezones)? By correctly i mean in their respective timezones?
For example team A in timezone UTC +4 enters a date with time 9:00, which is stored in the file as 5:00 UTC. Then when team B from timezone UTC -3 reads the file they should read 2:00. And so for every team?
Or are the datetimes inputted by just one person/team in one timezone and all the other teams just need to read the times correctly?

3. Will all the teams open the file with the desktop application? Or will they also open the file in the web app?

 

[elitef]

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 ???

The below is the code I have so far and you can see the comment areas as to what I am trying to achieve hopefully

Sub Convert()
Dim localTime As Date, utcTime As Date, ManualStartTime As Date, Convert As Date
Dim Offset As String, Unary As String
Dim timeDiff As Date
localTime = Now
utcTime = UTCNow()
timeDiff = localTime - utcTime
Unary = IIf(timeDiff < 0, "-", "+")
Offset = Unary & Format(timeDiff, "hh:mm")
ManualStartTime = Format(Range("A2").Value, "yyyy-mm-dd") & " " & Format(Range("B2").Value, "hh:mm")

Convert = ManualStartTime + (timeDiff / 24) 'should ultimiately covert the local computer timezone to UTC, but it doesnt appear to work

Range("C1").Value = "Format"    'header
Range("D1").Value = "Offset"    'header
Range("F1").Value = "Convert"   'header
Range("H1").Value = "GimmeUTC"  'header

Range("C2").Value = ManualStartTime 'combination of the manually entered date and time
Range("C2").NumberFormat = "yyyy-mm-dd hh:mm" 'same format as GimmeUTC

Range("D2").Value = Offset          'offset time from manually entered stamp compared to GimmeUTC stamp
Range("D2").NumberFormat = "hh:mm" 'format

Range("F2").Value = Convert 'should ultimately match the H2 field for a successful conversion from local computer timezone to UTC
Range("F2").NumberFormat = "yyyy-mm-dd hh:mm"   'format


Range("H2") = GimmeUTC 'GimmeUTC
Range("H2").NumberFormat = "yyyy-mm-dd hh:mm" 'format

End Sub

Just to understand the whole process.

1.
The file you are working on, will it be the same file which will be accessed by every team of every location? For example the file will be stored in the cloud (one drive, SharePoint or something similar) and everybody will actually be accessing the same file?
Or will everybody receive a copy of the file?

2. Do every team input some datetimes (in their timezone) and these datetimes should be read correctly by the other teams (in other timezones)? By correctly i mean in their respective timezones?
For example team A in timezone UTC +4 enters a date with time 9:00, which is stored in the file as 5:00 UTC. Then when team B from timezone UTC -3 reads the file they should read 2:00. And so for every team?
Or are the datetimes inputted by just one person/team in one timezone and all the other teams just need to read the times correctly?

3. Will all the teams open the file with the desktop application? Or will they also open the file in the web app?

1. Yes, it will be a sharepoint file therefore multiple people across the world will be accessing the same file
2. Yes, they will be entering time in THEIR local computer timezones and I expect the code to convert them to the same format yyyy-mm-dd hh:mm
3. Yes, the file should be opened within the desktop application, not within Excel web.

 

[felixstraube]

One way to solve this that comes to mind is this:

1. Your create a helper column or sheet (hidden i would say) where the actual datetimes are stored in UTC.
2. Then you use the "open workbook" event to convert the datetime stored in UTC to the timezone of the user an write it to the cell or cells that the user will be seeing and using. So they see the time in their timezone.
3. If the user enters a datetime you use the "change" event to convert it to UTC and store it to the hidden location.

This way the data is always stored in UTC and every user will see those dates in their respective timezones. Hope this makes sense.

A little later in the day, when i have a little more time, I can help you with the vba to do this if you like.

 

[elitef]

One way to solve this that comes to mind is this:

1. Your create a helper column or sheet (hidden i would say) where the actual datetimes are stored in UTC.
2. Then you use the "open workbook" event to convert the datetime stored in UTC to the timezone of the user an write it to the cell or cells that the user will be seeing and using. So they see the time in their timezone.
3. If the user enters a datetime you use the "change" event to convert it to UTC and store it to the hidden location.

This way the data is always stored in UTC and every user will see those dates in their respective timezones. Hope this makes sense.

A little later in the day, when i have a little more time, I can help you with the vba to do this if you like.

I'd be okay with a helper column, but how would it work? What would be the formula which would go into whichever cell in order to convert the timezone into UTC?

 

[felixstraube]

As user you would only see the datetime your are interested in and in your timezone in let's say cells in column A. You can update this datetimes (also in your timezone).
The vba code will do the conversion, no formula needed. When the book opens the code will read the utc time in the hidden helper sheet and convert it to the users timezone an write this to the cells in column A.
If the user adds, updates or deletes some datetime in column A, the vba code will convert it back to utc and store it to the hidden sheet.
So the data is always stored in utc and the user sees and enters data in his/her timezone.

I will write an example workbook and upload it for you to download and test.

 

[felixstraube]

Ok here is a working file for you to test:

GetUtcTime.zip

Where you have a "UserSheet":

GetUtcTime.xlsm
	A
1	Users timezone datetimes
2	2024-01-30 13:00
3	2024-01-31 10:00
4	2024-02-01 09:00
5	2024-02-02 14:00
6	2024-02-03 20:00
7	2024-02-04 23:00
8	2024-02-04 01:00
9
10
11
UserSheet

And an "Aux" sheet which can be hidden (i didn't hide it):

GetUtcTime.xlsm
	A	B
1	GimmeUTC()	2024-01-30 18:01
2	NOW()	2024-01-30 15:01
3	TimeZone [h]	-3
4
5
6		Stored datetimes in UTC
7		2024-01-30 16:00
8		2024-01-31 13:00
9		2024-02-01 12:00
10		2024-02-02 17:00
11		2024-02-03 23:00
12		2024-02-05 02:00
13		2024-02-04 04:00
14
15
16
Aux

Cell Formulas
Range	Formula
B1	B1	=gimmeUtc()
B2	B2	=NOW()
B3	B3	=ROUND((B2-B1)*24*4,0)/4

With the following VBA code:

Function GimmeUTC()
    Application.Volatile
    Dim dt As Object
    Set dt = CreateObject("WbemScripting.SWbemDateTime")
    dt.SetVarDate Now
    GimmeUTC = dt.GetVarDate(False)
End Function

Function convertUtcToTimezone(utcDatetime As Date) As Date
    Dim timezone As Double
    timezone = Worksheets("Aux").Range("B3").Value / 24
    convertUtcToTimezone = utcDatetime + timezone
End Function

Function convertTimezoneToUtc(userDatetime As Date) As Date
    Dim timezone As Double
    timezone = Worksheets("Aux").Range("B3").Value / 24
    convertTimezoneToUtc = userDatetime - timezone
End Function


Function readUtcWriteToUserCells()
    Dim utcCells As Range
    Dim userCells As Range
    Set utcCells = Worksheets("Aux").Range("B7:B16").Cells
    Set userCells = Worksheets("UserSheet").Range("A2:A11").Cells

    For i = 1 To utcCells.count
        If IsEmpty(utcCells(i, 1)) Then
            userCells(i, 1).ClearContents
        Else
            userCells(i, 1).Value = convertUtcToTimezone(utcCells(i, 1).Value)
        End If
    Next
End Function

Function readUserCellsWriteToUtc()
    Dim utcCells As Range
    Dim userCells As Range
    Set utcCells = Worksheets("Aux").Range("B7:B16").Cells
    Set userCells = Worksheets("UserSheet").Range("A2:A11").Cells

    For i = 1 To utcCells.count
        If IsEmpty(userCells(i, 1)) Then
            utcCells(i, 1).ClearContents
        Else
            utcCells(i, 1).Value = convertTimezoneToUtc(userCells(i, 1).Value)
        End If
    Next
End Function

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Intersect(Target, Range("A2:A11")) Is Nothing Then Exit Sub
    Application.EnableEvents = False

    Call readUserCellsWriteToUtc
    
    Application.EnableEvents = True
End Sub

For sure this can be optimized for your needs.
One thing that could be done is getting rid of the cells A1:B3 from "Aux", and do everything with VBA.
And I'm no pro VBA programmer. So there might be better ways to do thing. But it works.
Try it and let me know.

 

[elitef]

Ok here is a working file for you to test:

GetUtcTime.zip

Where you have a "UserSheet":

GetUtcTime.xlsm
	A
1	Users timezone datetimes
2	2024-01-30 13:00
3	2024-01-31 10:00
4	2024-02-01 09:00
5	2024-02-02 14:00
6	2024-02-03 20:00
7	2024-02-04 23:00
8	2024-02-04 01:00
9
10
11
UserSheet

And an "Aux" sheet which can be hidden (i didn't hide it):

GetUtcTime.xlsm
	A	B
1	GimmeUTC()	2024-01-30 18:01
2	NOW()	2024-01-30 15:01
3	TimeZone [h]	-3
4
5
6		Stored datetimes in UTC
7		2024-01-30 16:00
8		2024-01-31 13:00
9		2024-02-01 12:00
10		2024-02-02 17:00
11		2024-02-03 23:00
12		2024-02-05 02:00
13		2024-02-04 04:00
14
15
16
Aux

Cell Formulas
Range	Formula
B1	B1	=gimmeUtc()
B2	B2	=NOW()
B3	B3	=ROUND((B2-B1)*24*4,0)/4

With the following VBA code:

View attachment 105977

Function GimmeUTC()
    Application.Volatile
    Dim dt As Object
    Set dt = CreateObject("WbemScripting.SWbemDateTime")
    dt.SetVarDate Now
    GimmeUTC = dt.GetVarDate(False)
End Function

Function convertUtcToTimezone(utcDatetime As Date) As Date
    Dim timezone As Double
    timezone = Worksheets("Aux").Range("B3").Value / 24
    convertUtcToTimezone = utcDatetime + timezone
End Function

Function convertTimezoneToUtc(userDatetime As Date) As Date
    Dim timezone As Double
    timezone = Worksheets("Aux").Range("B3").Value / 24
    convertTimezoneToUtc = userDatetime - timezone
End Function


Function readUtcWriteToUserCells()
    Dim utcCells As Range
    Dim userCells As Range
    Set utcCells = Worksheets("Aux").Range("B7:B16").Cells
    Set userCells = Worksheets("UserSheet").Range("A2:A11").Cells

    For i = 1 To utcCells.count
        If IsEmpty(utcCells(i, 1)) Then
            userCells(i, 1).ClearContents
        Else
            userCells(i, 1).Value = convertUtcToTimezone(utcCells(i, 1).Value)
        End If
    Next
End Function

Function readUserCellsWriteToUtc()
    Dim utcCells As Range
    Dim userCells As Range
    Set utcCells = Worksheets("Aux").Range("B7:B16").Cells
    Set userCells = Worksheets("UserSheet").Range("A2:A11").Cells

    For i = 1 To utcCells.count
        If IsEmpty(userCells(i, 1)) Then
            utcCells(i, 1).ClearContents
        Else
            utcCells(i, 1).Value = convertTimezoneToUtc(userCells(i, 1).Value)
        End If
    Next
End Function

View attachment 105979

Private Sub Worksheet_Change(ByVal Target As Range)
   
    If Intersect(Target, Range("A2:A11")) Is Nothing Then Exit Sub
    Application.EnableEvents = False

    Call readUserCellsWriteToUtc
   
    Application.EnableEvents = True
End Sub

For sure this can be optimized for your needs.
One thing that could be done is getting rid of the cells A1:B3 from "Aux", and do everything with VBA.
And I'm no pro VBA programmer. So there might be better ways to do thing. But it works.
Try it and let me know.

Thanks for this.
I tried it but not quite sure that to do to actually try and see if it works lol sorry

Essentially, what I am trying to accomplish is:
User opens sheet, puts in time of issue in their timezone, and the time is converted to UTC in another cell

So if A1 is Date and B1 is time (January 30th, 2024 and 15:00) This would be in their timezone
Then C1 would calculate the offset between the combined date of 2024-01-30 15:00 and the current UTC time
Then in D1, it would convert the entered time by the user and display the time in UTC time. (not current UTC time)

 

[felixstraube]

That's exactly what the file does. Only that you enter date and time in the same cell instead of using 2 cells.

1. The user 1 enters date and time in a cell in the range A2:A11 in sheet "UserSheet". This time in the users timezone is then converted to utc and stored in the sheet "Aux". User closes workbook.
2. Another user 2 from another timezone opens the workbook. The datetime data in "Aux" is read, and converted from UTC to their timezone and written to the range A2:A11 in "UserSheet". So user 2 will see the datetime converted to his/her timezone.

Cells in "UserSheet" are for entering and displaying the date and time. The actual data is stored in "Aux" as UTC time.

Try this:
1. Delete all datetime data in "UserSheet".
2. Enter some datetime in A2 (for example "2024-01-31 8:00").
3. Go to "Aux" and see if that datetime is stored correctly converted to UTC.
4. Send the file to a coworker who is in another timezone, let him open the file and let him tell you what datetime he sees in A2 in "UserSheet".
(Instead of sending the file, you could just close the file, change the timezone of your computer to something different than your actual timezone, and open the file again and see how the datetime in A2 in "UserSheet" changes to that new timezone.

 

[elitef]

That's exactly what the file does. Only that you enter date and time in the same cell instead of using 2 cells.

1. The user 1 enters date and time in a cell in the range A2:A11 in sheet "UserSheet". This time in the users timezone is then converted to utc and stored in the sheet "Aux". User closes workbook.
2. Another user 2 from another timezone opens the workbook. The datetime data in "Aux" is read, and converted from UTC to their timezone and written to the range A2:A11 in "UserSheet". So user 2 will see the datetime converted to his/her timezone.

Cells in "UserSheet" are for entering and displaying the date and time. The actual data is stored in "Aux" as UTC time.

Try this:
1. Delete all datetime data in "UserSheet".
2. Enter some datetime in A2 (for example "2024-01-31 8:00").
3. Go to "Aux" and see if that datetime is stored correctly converted to UTC.
4. Send the file to a coworker who is in another timezone, let him open the file and let him tell you what datetime he sees in A2 in "UserSheet".
(Instead of sending the file, you could just close the file, change the timezone of your computer to something different than your actual timezone, and open the file again and see how the datetime in A2 in "UserSheet" changes to that new timezone.

Great. Thank you very much man!
I was able to customize it to bring it all together the way I need in a single sheet (see below).

So, the below will be the START DATE/TIME for example. Now, if I were to add another row, say instead of working with Row 1 and 2, now I want to do the same on Row 4 and 5 for the END DATE/TIME, would I just duplicate the below and rename the 2 subs that I have to be unique or is there a way to do it so that it does it automatically?

Function GimmeUTC()
    Application.Volatile
    Dim dt As Object
    Set dt = CreateObject("WbemScripting.SWbemDateTime")
    dt.SetVarDate Now
    GimmeUTC = dt.GetVarDate(False)
End Function

Function convertTimezoneToUtc(userDatetime As Date) As Date
    Dim timezone As Double
    timezone = (Round((Now - GimmeUTC()) * 24 * 4, 0) / 4) / 24
    convertTimezoneToUtc = userDatetime - timezone
End Function

Private Sub readUserCellsWriteToUtc()

    Dim utcCells As Range
    Dim userCells As Range
    Set utcCells = Range("E2").Cells
    Set userCells = Range("C2").Cells

    For i = 1 To utcCells.count
        If IsEmpty(userCells(i, 1)) Then
            utcCells(i, 1).ClearContents
        Else
            utcCells(i, 1).Value = convertTimezoneToUtc(userCells(i, 1).Value)
        End If
    Next
    
End Sub

Private Sub Convert()

Range("C1").Value = "Local Format"    'header
Range("C2").Value = Format(Range("A2").Value, "yyyy-mm-dd") & " " & Format(Range("B2").Value, "hh:mm") 'combination of the manually entered date and time
Range("C2").NumberFormat = "yyyy-mm-dd hh:mm" 'format

Call readUserCellsWriteToUtc

Range("E1").Value = "UTC Format"    'header
Range("E2").NumberFormat = "yyyy-mm-dd hh:mm" 'UTC format

Range("F1").Value = "UTC Date"    'header
Range("F2").Value = Range("E2").Value 'Extract from UTC format
Range("F2").NumberFormat = "mmmm dd, yyyy" 'same format as GimmeUTC

Range("G1").Value = "UTC Time"    'header
Range("G2").Value = Range("E2").Value 'Extract from UTC format
Range("G2").NumberFormat = "hh:mm" 'same format as GimmeUTC

End Sub

 

[felixstraube]

You are very welcome. Glad you could solve it.

So, the below will be the START DATE/TIME for example. Now, if I were to add another row, say instead of working with Row 1 and 2, now I want to do the same on Row 4 and 5 for the END DATE/TIME, would I just duplicate the below and rename the 2 subs that I have to be unique or is there a way to do it so that it does it automatically?

Can you show me how you want your sheet to look like and how your data input is going to change/grow? Or you just need it to work for rows 1,2 and 4,5?