Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Record hh:mm:ss on click of cell
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? -- Cheers! Mrbrently |
#2
|
|||
|
|||
Hi Mrbrently,
I tried using =now() and a macro. Couldn't get the required result. Any ideas? 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 |
#3
|
|||
|
|||
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. -- When sending mail, use address arvil<attarkon.ee Arvi Laanemets "Mrbrently" wrote in message ... 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? -- Cheers! Mrbrently |
#4
|
|||
|
|||
works perfectly--thanks!
"Mrbrently" wrote: 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? -- Cheers! Mrbrently |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Click on cell-calendar drops down-click on date-date fills cell. . | Setting up and Configuration of Excel | |||
up to 7 functions? | Excel Worksheet Functions | |||
Syntax for inferred cell references | Excel Worksheet Functions | |||
Excel should have a feature just right click on a cell and get it. | Excel Discussion (Misc queries) | |||
Can I use the contents of a cell to satisfy the result_vector arg. | Excel Worksheet Functions |