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

 

[elitef]

You are very welcome. Glad you could solve it.



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?

Only:
Row 1 (headers)
Row 2 (values/output)
Row 3 spacer
Row 4 (headers)
Row 5 (values/output)

Rows 1 and 2 will be for START DATE/TIMESTAMP
Rows 4 and 5 will be for END DATE/TIMESTAMP

Columns will be the same as well as the previous code. A:C and E:G, while D stays as a spacer

 

[felixstraube]

Try this:

Function readUserCellsWriteToUtc()
    Dim userRange As Range
    
    Set userRange = Union(Worksheets("UserSheet").Range("A2"), Worksheets("UserSheet").Range("A5"))
    
    For Each cell In userRange.Cells

        If IsEmpty(cell) Then
            cell.Offset(0, 4).ClearContents 'clear data in column E
        Else
            cell.Offset(0, 4).Value = convertTimezoneToUtc(cell.Value + cell.Offset(0, 1).Value)
        End If
    Next
End Function

 

[felixstraube]

if you need more input cells and write to the same column E, than just add the cells with the union function, for example

Set userRange = Union(Worksheets("UserSheet").Range("A2"), Worksheets("UserSheet").Range("A5"), Worksheets("UserSheet").Range("A8"))

 

[felixstraube]

An easier way of doing the union is this:

Set userRange = Worksheets("UserSheet").Range("A2, A5")

or

Set userRange = Worksheets("UserSheet").Range("A2, A5, A8")

or

Set userRange = Worksheets("UserSheet").Range("A2, A5, A6:A8")

 

[elitef]

Thanks, I'll have to give it a try since I've changed your script a bit from before to what I posted above, so I'll have to see how I can make the above that you posted work in my script.

 

[felixstraube]

Just replace the readUserCellsWriteToUtc() function.
Change the name of the sheet to you sheet name.
The range union:

Set userRange = Worksheets("UserSheet").Range("A2, A5")

should point to al the cells containing dates in column A. Then it will get the time from the cell to the right, and finally will write to the cell 4 cells to the right (column E).