How to insert UTC Time into a cell

[rskip]

I need a cell with UTC Time so as I can reference to other cells with different time zones. I know how to figure the different time zone time from UTC. So do I need VBA to do that, and what is the code. It would be great if it auto updated also.
Thanks using Office 365.

 

[Domenic]

After searching Google, I came across the following link in which it describes how to get the current UTC date and time...

How to get the current datetime in UTC from an Excel VBA macro

Is there a way in an Excel VBA macro to get the current datetime in UTC format? I can call Now() to get the current time in the local timezone; is there a generic way to then convert this to UTC?

stackoverflow.com

Accordingly, first add the following custom function to a regular module...

Function GimmeUTC()
    ' Returns current date/time at UTC-GMT as an Excel serial date value
    Dim dt As Object
    Set dt = CreateObject("WbemScripting.SWbemDateTime")
    dt.SetVarDate Now
    GimmeUTC = dt.GetVarDate(False)
End Function

Then you can enter the following worksheet formula to get the current UTC date and time...

=GimmeUTC()

Hope this helps!

 

[rskip]

Great Thanks works perrrrfect !

 

[rskip]

Great Thanks works perrrrfect !

Now any chance its just as easy to add a auto refresh function?

 

[Domenic]

You can add Application.Volatile at the beginning of the custom function...

Function GimmeUTC()
    ' Returns current date/time at UTC-GMT as an Excel serial date value
    Application.Volatile
    Dim dt As Object
    Set dt = CreateObject("WbemScripting.SWbemDateTime")
    dt.SetVarDate Now
    GimmeUTC = dt.GetVarDate(False)
End Function

Now the worksheet formula will automatically re-calculate whenever there's a change in any cell within the workbook, or you calculate the sheet using Shift+F9, or you calculate the workbook using F9.

 

[rskip]

I must be doing something wrong as it does not refresh even using shift +F9

 

[Domenic]

I'm assuming that you've added Application.Volatile to the code, correct?

How did you format your cell?

You should be able to update the formula by using Shift+F9 if the sheet containing the formula is the active sheet, or by using F9 to calculate the entire workbook.

 

[rskip]

Thanks I formatted the cell on a different tab, so when I press F9 on that tab, all is good, thanks for your help!!!

 

[Domenic]

You're very welcome, cheers!

 

[drWireMORE]

This works perfect, and thank you. As a nit: I have a cell formatted in Z time as: yyyy-mm-ddThh:mm:ss.000Z

GimmeUTC does not offer the added decimal precision.
=now() local 2024-03-14T09:19:05.980Z (yeah, the Z is forced by the format, but this is local time)
=gimmeUTC() 2024-03-14T14:19:05.000Z (and while the hrs are adjusted to Z, thank you, hrs, minutes, but no decimal)

The question: is there an easy way to "add" my missing decimal seconds to gimmeUTC?

I've run down the rabbit hole of trying to truncate, or add, and haven't stumbled on a combination that works.

Just thought it odd that the decimal seconds is not provided despite being formatted the same.

drW

(PS, you are one of my go-to for expert Excel advice, thank you)