excel display milliseconds high precision code example
Example: excel display milliseconds high precision
'In Excel, to display milliseconds but leave the date/times
'values as numbers, use Number Formats on the range:
Select Range-->Format Cells-->Number-->Custom-->[hh]:mm:ss.000
'Or from VBA:
[a1].NumberFormat = "[hh]:mm:ss.000"
'---------------------------------------------------------------------
'But if you need to actually change the data,
'not just how it displays...
'VBA to transform sub-second date/times to text:
Public Function FormatDatePrecision$(d#)
On Error Resume Next
FormatDatePrecision = Format(d / 86400, "yyyy-mm-dd HH:mm:ss")
FormatDatePrecision = FormatDatePrecision & "." & Split(d, ".")(1)
End Function
MsgBox FormatDatePrecision(3794741793.437)
'Displays: 2020-03-30 14:56:33.437
'VBA to transform sub-second text to Excel date/times:
Public Function CDatePrecision#(d$)
On Error Resume Next
CDatePrecision = CDbl(CDate(Split(d, ".")(0)) * 86400)
CDatePrecision = CDatePrecision + CCur(Split(d, ".")(1) / 1000)
End Function
MsgBox CDatePrecision("2020-03-30 14:56:33.437")
'Displays: 3794741793.437