Read Or Return Time In Displayed Format

[OzTrekker]

Is there a way to read a time value from a cell in the same format it is displayed in?

I have cells formatted as mm:ss.00.
When I read the cell value via macro it returns the time serial number.
I've tried formatting this serial number in VBA as follows Format(A1, "hh:mm:ss.00") before outputting it.
Unfortunately it always returns the time without the decimal places on the seconds component
So 02:07.35 is returned as 02:07.00 and 03:05.87 is returned as 03:06.00.
Any ideas?

[Kramxel]

You could do it with a formula by using :

Please Login or Register  to view this content.

note: [HH] adds hours rather than rolling on to the day counter i.e. stops 25 hours shows as 25:MM:SS, rather than DD:01:MM:SS.

You could just apply basic excel formatting over the cell to obtain the same result.

VBA would be

Please Login or Register  to view this content.

[Oppressed1]

I've had no success working with fractional seconds so I'm interested in this. As to the TEXT above, I "manually" custom format A1 as
[hh]:mm:ss.00
Then I type into A1
1:02.33
And it does display
00:01:02.33
a2=text(a1,"[hh]:MM:SS.00")
does show the same thing. (Note, the answerer left off the .00 after the SS)
However if you hit Function key 2 then hit enter on cell A1, the fractional seconds are destroyed (also shown in A2 after doing that).
(Side note or trivia, SECOND() by definition only returns integers, even if there's a fractional piece.)

Back to VBA, the above answer is malformed (needs parentheses around "Sheet1"). More importantly, it doesn't report the fractional piece. Changing SS to SS.00 is no help.

OzTrekker, you might note that VBA format() just mimics the .NN you type after SS. When you tried SS.00 it just literally reported "00" at the end. So if you had gone SS.55 it would report .55 for seconds, like a literal text ".55" (when you instead want to see .87).

I'm also waiting for the answer; AAR I believe that the above answer is incorrect.

[OzTrekker]

Oppressed1 I agree with you 100%.

I'm reading the time then outputting it into a .txt file for subsequent transfer to another system.
Consequently I need to be able to format it correctly via VBA.
However no matter what I try it rounds the time up or down to the nearest second.

Surely there is a solution to this annoying problem!

[Kramxel]

sorry been away for a few days, came back to this this morning, and you're right

Please Login or Register  to view this content.

won't work but this should (or at least does for me)

Please Login or Register  to view this content.

however having read your post (properly this time), the custom format

Please Login or Register  to view this content.

allowed me to manually enter a time to the 10th of a second and display accurately.

via vba this would be:

Please Login or Register  to view this content.

EDIT::
You need to be careful of resolution, the VBA function now() seems to be accurate to 1 second where the worksheetfunction [now()] appears to be at least accurate to tenths of seconds. depending on how you're applying or obtaining the time you may need to look at different sources for the time data.

[OzTrekker]

Perfect!

You cracked it Kramxel.

My line of code from the "For" loop is:

Please Login or Register  to view this content.

This outputs "MM:SS.00" to the text file as required.
So 03:23.66 is output as 03:23.66 and not 03:23.70.

Thank you.

[Kramxel]

you're welcome, sorry i completely misread your post the first time around.

[Oppressed1]

I have been told that VBA Format() does not recognize square brackets []. It appears to silently discard them.

That said, you CAN use Format(), but don't apply it to .Value; use .Text
?format(range("A1").value,"HH:MM:SS") 'No good, fractional seconds lost
?format(range("A1").text,"HH:MM:SS") 'Good, shows fractional seconds

Of course since you've set the number format in Excel (or .NumberFormat), there's simply
?range("A1").text

But if the square brackets are vital, I like his solution which I've compacted as
application.text(range("a1"),"[HH]:MM:SS.00")
Note that it works properly with the implied .Value. It doesn't require using .Text, as Format() required above.

And he's right, if you were operating off of current time, the VBA Now function is "crude" compared to the Evaluated worksheet function. Other methods for operating at millisecond level with current time are shown at
http://vbadud.blogspot.com/2008/10/e...nds-using.html

Remember, the fractional seconds are lost if you select A1 and then nonchalantly go F2 and enter; 00:01:02.33 becomes 00:01:02.00. I stress it again because it's such a devastating "feature."