How to clock fractions of seconds in Excel

[TimeKeeper]

I'm in Excel 2013 (on Windows 7 and Windows 10).

I use the following Macro by tapping Ctrl+Shift+T to get H:mm:ss in any cell that is formatted in that same way.


Sub Time_Stamp()
' Keyboard Shortcut: Ctrl+Shift+T
ActiveCell.Value = Time()
Selection.NumberFormat = "h:mm:ss"
End Sub

This has been working fine, but now, I need fractions of seconds...

--------------------------------------------------------------------------

I have changed the number format to "h:mm:ss.000" in the macro AND in the format of my cells.

Unfortunately I'm only getting the time like this: 13:49:30.000 -- instead of the 3-digit fractions of seconds that I want.

------------------------------------------------------------------------------

What can I do to get fractions of seconds. I am clocking time very quickly and need this to happen instantly with my keyboard shortcut.

Thanks for your help!!!

 

[pgc01]

Hi
Welcome to the board

Try something like:

    Range("A1").NumberFormat = "hh:mm:ss.000"
    Range("A1").Value = Timer / 86400

 

[TimeKeeper]

Mr. Excel,

Thank you for your reply. Unfortunately, it did not work for me.

Using your example, I want the time I clock to have h:mm:ss.000 (where the 000 represents fractions of seconds).

I want that to be an instantaneous entry in any number of cells that I have formatted to that same description (h:mm:ss.000)

Here is an example of my normal use, with only h:mm:ss, where the IN and OUT points are entered with my CONTROL+SHIFT+T as I'm clocking events. The TRT is a simple calculation result.

TC IN	TC OUT	TRT
7:41:08	7:41:11	0:00:03
7:41:44	7:42:49	0:01:05
7:42:52	7:44:55	0:02:03

<tbody>
</tbody>

What I want is something like this:

7:41:08.125	7:41:11.250	0:00:03.125

<tbody>
</tbody>

I modified my Time_Stamp macro (from what I have shown in the thread above, and including your suggestion) to look like this:

Sub Time_Stamp()
' Keyboard Shortcut: Ctrl+Shift+T
ActiveCell.Value = Time / 86400
Selection.NumberFormat = "h:mm:ss.000"
End Sub

What I get when I clock in my cells, I get this:

0:00:00.332

0:00:00.332

0:00:00.000

<tbody>
</tbody>

Another note.
If I simply format my cells as shown (h:mm:ss.000) and type in 7:49:22.250 7:50:23.750, I'm obviously looking for a calculation (TRT) of 0:01:01:500... But for some reason, the simple subtraction returns a #VALUE error.

Thanks for any additional help!

 

[shg]

ActiveCell.Value = Time / 86400

Not Time, Timer

Timer returns the number of seconds since midnight, with precision of 1/64th or 1/256th of a second.

 

[joeu2004]

@TimeKeeper.... You missed a subtle difference, namely: Timer v. Time.

You used the VBA Time function. That returns time of day as Excel time truncated to the second, as a fraction of a day. That is, (h + m/60 + s/3600)/24. No matter how you format or calculate it, the fraction of a second is zero.

Pgc01 uses the VBA Timer function. On Windows PCs, that returns time of day as seconds truncated to 1/64 second (usually). The precision is a microsecond. The format "h:m:s.000" rounds to the millisecond.

I say "usually" because 1/64 is the default system clock update interval, but applications can change the system clock update interval, thereby changing the accuracy of Timer if they are executing concurrently. Also, on Mac PCs, VBA Timer returns time of day as seconds truncated to the second, according to VBA help. I don't know if that is still true. MSFT documentation is often incorrect (!).

A note about VBA Timer precision.... Since VBA Timer returns type Single, not Double, the binary truncation gives the illusion that the value is not a multiple of 1/64. Round(Timer*64)/64 is the more type Double representation of seconds accurate to 1/64 second. But I suspect that Timer/86400 is accurate enough for representing time of day.

-----

Another subtle, but important difference....

You set the cell format to "h:mm:ss.000" after assigning the cell value.

Pgc01 sets the format before assigning the value.

Pgc01's approach is more reliable. But it makes a difference only if the cell format is Text before executing the code fragment.

-----

You use ActiveCell in one statement and Selection in the other.

It would be prudent to be consistent. And I suspect ActiveCell is better for your purposes.

It makes a difference only (?) when Selection is a range of more than one cell, perhaps accidentally.

-----

I have no problem with typing 7:49:22.250 into A1 and 7:50:23.750 into B1, and calculating =B1-A1, all formatted as [h]:mm:ss.000 .

I suspect that you entered one or both times preceded by spaces, or you copy-and-pasted from a presentation (e.g. HTML) that included non-breaking spaces (ASCII 160).

 

[TimeKeeper]

As best I understand all of your notes... I changed my macro to read:

Sub Time_Stamp()
' Keyboard Shortcut: Ctrl+Shift+T
ActiveCell.NumberFormat = "h:mm:ss.000"
ActiveCell.Value = Timer / 86400
End Sub

This is what I get:

(triangle, exclamation point) Compile Error: Expected Function or variable.

If I enter the macro thusly:

Sub Time_Stamp()
' Keyboard Shortcut: Ctrl+Shift+T
ActiveCell.NumberFormat = "h:mm:ss.000"
ActiveCell.Value = Time / 86400
End Sub

I get:

0:00:00.490

0:00:00.491

0:00:00.000

<tbody>
</tbody>

Regarding this portion of your reply:

<<<I have no problem with typing 7:49:22.250 into A1 and 7:50:23.750 into B1, and calculating =B1-A1, all formatted as [h]:mm:ss.000 .

I suspect that you entered one or both times preceded by spaces, or you copy-and-pasted from a presentation (e.g. HTML) that included non-breaking spaces (ASCII 160).>>>

The first sentence does represent my =sum function. I don't enter any data in any other manner than by "clocking" at the precise moment I'm trying to get my time, by clicking on my Time In cell (A1, for example) and selecting CONTROL+SHIFT+T (as shown in my macro). Then I move to the Time Out cell (B1, for example) and "clock" again as needed with the same keyboard shortcut.

But, even in 3 cells that are formatted h:mm:ss.000, when I do specifically type in some test numbers, such as:

11:55:33.492

11.58.34.523

#VALUE!

<tbody>
</tbody>

Well... you can see that my =sum produces an error...

I'm sorry if I'm being dense, and not fully understanding your responses, and I appreciate your help.

Can you tell me how to correct my macro, please?

Thank you

 

[joeu2004]

Sub time_stamp()
' keyboard shortcut: Ctrl+shift+t
activecell.numberformat = "h:mm:ss.000"
activecell.value = timer / 86400
end sub

this is what i get:
(triangle, exclamation point) compile error: Expected function or variable.

When I copy-and-paste the macro from your posting into a worksheet module, I get no error when I execute the macro, and A1 displays 13:44:21.031, which is indeed about the time that I performed those actions.

If you still cannot figure out your mistake, I suggest that you upload an example Excel file (redacted) that demonstrates that error to a file-sharing website, and post the public/share URL in a response here. Test the download URL first, being careful to log out of the file-sharing website.

PS.... It might help to know if you are using a Windows PC or a Mac. I don't know anything about the latter. AFAIK, the only problem it might create is that VBA Timer does not return fractional seconds either, according to VBA help.

Sub time_stamp()
' keyboard shortcut: Ctrl+shift+t
activecell.numberformat = "h:mm:ss.000"
activecell.value = time / 86400
end sub

i get:

0:00:00.490

0:00:00.491

0:00:00.000

<tbody>
</tbody>

As I explained, VBA Time returns time of day as a fraction of a day. 0.490 and 0.491 represent the approximate TODs 11:45:36 and 11:47:02, which is a little before your posting at 11:59. But you divide the fractional day by 86400, treating it as seconds. Hence, the values become 0.490 and 0.491 "seconds", as your format displays.

But, even in 3 cells that are formatted h:mm:ss.000, when i do specifically type in some test numbers, such as:

11:55:33.492

11.58.34.523

#value!

<tbody>
</tbody>

Unless you have a typo in your posting, 11.58.34.523 [sic] is not a valid time specification. The first periods should be colons, namely: 11:58:34 .

Well... you can see that my =sum produces an error...

Aside.... I hope you are not writing a formula that uses Excel SUM. Your formula should be of the form =B1-A1 .

 

[TimeKeeper]

joeu2004,

Thank you, again, for your reply!

First, you were absolutely right; my example time entry contained typos that I hadn't even noticed (where there were periods when there should have been colons). I don't usually type my times in by hand, as I use the keyboard shortcut. Once, I corrected that, the calculation was solved! Mad at myself for not catching that!

Also, since you said that my macro worked for you... I opened a completely new Excel file (I am on a PC Windows 10 Pro... Sorry, I thought I had posted that in my original request for help)... and entered the macro.

Everything is working just as I need it to!

Apparently, another macro in my original files (which I use as templates that I simply create new documents from for use every day)... that must be creating some kind of conflict with the Timer aspect of this one.

I have a macro that I use as a Countdown Clock in my A1 cell... So, I can set the A1 cell to read as, say, 4:00 minutes, and run the macro to get a countdown... to 0:00:00

Anyway, I can live without the countdown until I can figure out what the conflict is. I am not that experienced with Excel macros... just some of the more simple functions.

I have changed my calculation, per your note. But I am curious what is wrong with =sum(B1-A1)...?

Again, THANK YOU SO VERY MUCH FOR YOUR HELP!
I appreciate your assistance!

 

[joeu2004]

Nothing is wrong with =SUM(B1-A1). It is just superfluous, with no benefit whatsoever.

As for the countdown code, it should not conflict with the use of VBA Timer; at least not by its nature. But I have seen people use VBA function names for their own variables and procedures. That could be source of the conflict. Unfortunately, VBA let's us do that.

Here's a trick for the future.... When you invent a new variable or procedure name that is a common word, type the first use of it in all lower case initially. When you press Enter, if VBA changes the capitalization automatically, that means VBA uses that name in some context, and you should avoid it. If it remains all lower case, you can go back and capitalize it the way that you want.

I do make one exception. I commonly use variable names like x, y and z. When I enter z for the first time, VBA capitalizes it; perhaps because it is a property of something called ThreeDFormat. I'm not going to avoid a one-letter name just because VBA uses it for some obscure thing. AFAIK, z is the only one-letter name that's special.

Anyway, glad to hear that all is well now. You're very welcome.