Record hh:mm:ss on click of cell

M

Mrbrently

I'm a school sports coordinator and trying to set up an XL spreadsheet to
record the Triathlon and Cross Country finishers. Hit a button for time on
the laptop as they cross the line and put down their number. A quick filter
when all the competitors are in and strait into prize giving. Wouldn't that
be fantastic.
Would you know how to do this?

If you use ctrl+shift+: you only get hours and minutes. I need seconds.
I tried using =now() and a macro. Couldn't get the required result.
Any ideas?
 
A

arno

Hi Mrbrently,
I tried using =now() and a macro. Couldn't get the required result.
Any ideas?
Click to expand...

sure, try

Sub Makro1()
Range("a1").NumberFormat = "dd/mm/yyyy hh:mm:ss"
Range("a1").Value = Now
End Sub

I think you just have the wrong number format in your sheet, add the
seconds :ss to your format.

regards

arno
 
A

Arvi Laanemets

Hi

Create an Excel workbook.
On some sheet, enter:

A1="StartTime:"
A2="Nr"
B2="ID" (or "Name")
C2="Time"
F1="NextNr:"

Format cell C1 as "h:mm:ss"
Format cells C3:C4## as "mm:ss", where ## is any number >3, you think as
resonable.
For cell G1, use data validation: allow whole numbers only between 1 and ##,
where ## is any number >1, you think as reasonable.

Create 2 named ranges:
StartTime=Sheet1!$C$1
NextNr=Sheet1!$G$1

Activate VBA editor (Alt+F11). Insert a module. Insert 2 procedures into
this module

Public Sub InsertStartTime()
Range("C1").Value = Time
End Sub

Public Sub InsertFinishTime()
Nr = IIf([NextNr] = "", 1, [NextNr])
Range("A2").Offset(Nr, 0).Value = Nr
Range("A2").Offset(Nr, 2).Value = Time - [StartTime]
Range("G1").Value = IIf(Nr = "", 1, Nr) + 1
End Sub

From View menu, activate Forms toolbar.
Insert a button (p.e. over cells D1:E1), link it to procedure
InsertStartTime, and rename the button text to "Start the clock"
Insert a button (p.e. over cells D2:E2), link it to procedure
InsertFinshTime, and rename the button text to "Register finish time"

The sheet is ready for use. Before to use it, enter the order number of
finisher, you want to start to register times for (p.e. 1) into cell G1,
and clear all data from range A3:C## (finish order numbers and finishing
times), when there were some.
To register the start time, click on button "Start the clock". The start
time is inserted into cell C1.
When first runner finishes, click on button "Register finish time". The
order number (1) is inserted into cell A3, and end time (=system time-start
time) is inserted into cell C3. Ito cell B3 you can enter competitors number
or name, when you have enough time for it. And the value of NextNr in cell
G1 is increased by one. The sheet is ready for next competitor.
When second runner finishes, click on button "Register finish time" again.
And so on.

I didn't find any possibiolity to register times in VBA with decimals of
second. Although it is possible using NOW() function in cell (you have to
use custom cell format to see it), but it seems VBA doesn't support the use
of NOW() with Application.WorksheetFunction, so I didn't find a way to
register the time more exactly.
 
Top