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.

 

[drWireMORE]

Potential answer, but ugly. I persevered, brute force, with the understanding the decimal part of an excel date/time is the hrs, minutes, seconds. And I just want the decimal seconds. Not offering this as the right way to do it, but it at least gave me the added decimal precision. Expectation is you already have the GimmeUTC function working. No, I didn't try to simplify it, just happy it is working.

=GimmeUTC()+(((NOW()-TRUNC(NOW()))*24*60*60) -TRUNC((NOW()-TRUNC(NOW()))*24*60*60))/(24*60*60)

1) You use truncate to leave you with the decimal portion of the time. (this would be some part of a day in hrs, minutes, seconds as decimal number)
2) Then truncate again having created a huge decimal number by X the above by hrs, mins, seconds (24,60,60) which should be xxxxx.yyyyy seconds. I just want the yyyy part.
3) Hopeful we have a number now that is exactly the missing decimal value, but we need to scale it back by dividing by hrs, mins, seconds (24,60,60) to get decimal seconds in excel time.

I could have made a single number (24x60*60) but then you'd want to know where it came from. So I kept it simple, and obvious.

drW

Or maybe, there is a feature/function/switch somewhere to actually enable the decimal precision Not everyone needs this type of precision, I just happen to need/want it.

 

[drWireMORE]

Interestingly, while this worked on my windows machine, produces a #value! error on my Mac.

Took a working spreadsheet with the function defined in VBA and working on my windows machine. Then opened the same file and it gave a value error.

There are two other features, both macros, unrelated to the error as they work, but sharing that I did enable macros. For whatever reason, generates an error. Perhaps something about the behind the scenes in excel on a Mac (Apple). The test cell has nothing in it except for GimmeUTC().

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