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
Are there any code examples left?
New code examples in category VBA
-
VBA 2022-03-27 20:55:02 excel vba check if all substrings in list are in string
-
VBA 2022-03-27 20:40:39 excel vba set cell value to empty
-
VBA 2022-03-27 17:05:52 vba simple quote
-
VBA 2022-03-27 14:30:19 textbox find and replace vb
-
VBA 2022-03-27 09:10:21 nested if else in vb.net
-
VBA 2022-03-27 09:10:09 excel vba string to bits
-
VBA 2022-03-25 02:00:15 vba verticalalignment
-
VBA 2022-03-24 12:20:06 Excel web scraping
-
VBA 2022-03-24 10:11:00 excel vba BitToLong
-
VBA 2022-03-21 18:00:05 excel vba save file txt