vba display milliseconds high precision code example

Example: vba 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