how to convert time eg 11:33:59 to text within a macro

[oldbrewer]

hello everybody and thanks in advance - I wish to use a macro to determine present time using now() and then convert that value to text within the macro. I do not wish to use helper cells on the spreadsheet. I am using excel 2000.

 

[Joe4]

Try using the FORMAT function, i.e.:

    Dim t As String
    t = Format(Now(), "hh:mm:ss")
    MsgBox t

 

[oldbrewer]

thanks Joe4, will try it straight after lunch (1 pm in UK)

 

[oldbrewer]

it works for hh:mm:ss but I want to get to hundredths of a second, on a spreadsheet this would be formatted as hh:mm:ss.000

thanks

 

[Joe4]

it works for hh:mm:ss but I want to get to hundredths of a second, on a spreadsheet this would be formatted as hh:mm:ss.000

thanks

That was not what you asked for in your original request (hundredths of seconds)!

I don't think VBA has a format for hundredths of seconds.
I think you may need to create your own User Defined Function for it, like shown here: Excel VBA TimeStamp – Milliseconds using Excel VBA

 

[oldbrewer]

thanks

 

[dermie_72]

Did you try using the above code, just modified to this?

Dim t As String
    t = Format(Now(), "hh:mm:sssss")
    MsgBox t

 

[oldbrewer]

I am not very good with VBA - retired 16 years ago, 78 years old. Never used message boxes. Can I just use the middle line beginning t = ?
Thanks for trying to help. I failed with format(now(),"hh:mm:ss.000")

 

[dermie_72]

format changes the first statement in the number format of the second statement.
First Statement: Now() means to capture the exact time the code is run
Second Statement: "hh:mm:sssss" gives you a time format of hour in 2 digit, Minute in 2 digits and seconds in 5 digits.
the msgbox just gives you a printout of the value on the screen.

 

[Alex Blakenburg]

Just to round off the discussion, I think there are 2 separate issues.
1) VBA Now()
Does not seem to capture time to the millisecond - the link @Joe4 provided in post #5 discusses this and some options best accuracy apparently being around 15ms.
Excel Now() does but I don't know if that is just a floating point error.
2) VBA Format
Does not have a format for milliseconds
3) Excel Text
Does have a format for milliseconds

My testing for 2 & 3

'test data
'22/03/2024  10:22:28.260
'45373.4322715278 (numeric value)

Sub testVBAvsExcel()

    Dim t As String
    t = Format(45373.4322715278, "hh:mm:ss.000")
    Debug.Print "Using VBA Format: ", t
    t = WorksheetFunction.Text(45373.4322715278, "hh:mm:ss.000")
    Debug.Print "Using Excel Text: ", t

End Sub

Output
Using VBA Format: 10:22:28.000
Using Excel Text: 10:22:28.260