User Defined Function for UTC doesn't display decimal seconds

[drWireMORE]

This is a working UDF, and I see at least two people have offered this solution, so kudos to both GimmeUTC() and UTC() in a windows environoment

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

However, while now() displays with decimal seconds, the above does not. And yes the format is the same.

I have a non-elegant method which fixed that by stripping the decimal seconds with an associated now() formula and some math.

Question: Is there a way to get a UDF format as above to just include decimal seconds, or is that a limitation of the Microsoft object?

My non-elegant solution: Yeah, I could simplify some of the math, but this made it more apparent.

=GimmeUTC()+(((NOW()-TRUNC(NOW()))*24*60*60) -TRUNC((NOW()-TRUNC(NOW()))*24*60*60))/(24*60*60)
strips all but the decimal (hrs,min,sec) strips all but the decimal seconds and then calibrates it back to decimal seconds
yes, one could simpify 24*60*60 but this makes it apparent that converting fraction of a day into seconds by x (24) hrs x (60) minutes/hr x to (60)seconds/min

 

[rlv01]

Since the function returns a date you can format the cell to display fractional seconds, but the problem is that I do not believe the VBA NOW function will return anything but whole seconds. You might consider using a vba function to instead calculate the UTC offset, and use that with the Now() function to get the resolution you need.

Book1
	A	B
1	Time	UTC Time
2	01:14:13.460 PM	07:14:13.460 PM
Sheet6

Cell Formulas
Range	Formula
A2	A2	=NOW()
B2	B2	=NOW()+UTC_Offset()

Function UTC_Offset() As Double
    With CreateObject("WbemScripting.SWbemDateTime")
        .SetVarDate Now
        UTC_Offset = .GetVarDate(False) - Now
    End With
End Function