I need Excel to return a time in hh:mm:ss when I enter a rider number in the adjacent cell.

Example : Where rider numbers are entered into the “A” column as they cross the line

In B1 =if(A1=””,””,NOW())
In B2 =if(A2=””,””,NOW())
In B3 =if(B3=””,””,NOW())
…and so on.

However, every time I enter anything the whole sheet recalculates the NOW() rather than just the dependant of the cell that the new entry has gone in.

The idea is that I’d enter a rider number as they cross the line and it would compile a list of numbers against times. The data is transferred into a table or a list that can be sorted to retrieve whatever stats I’m after.

Why do you need the NOW function?

To record the exact time that the number is entered. Perhaps there is a more suitable function… dunno… stumped.

use a macro and just one NOW

Sorry, I just turned my brain on. Your problem is that NOW is a live function. it’ll recalculate to always give you NOW. You could hardcode it each time you’ve used it.

Have you got to record a lot of times?

hence use a macro to grab your now data and store it as a fixed datapoint in a list.

so, Id have at top of list in row 1

enter rider number in A1, have formula =NOW() in B1

then stick this in your VBA

Sub logtimedata()
‘
‘ logtimedata Macro
‘ Macro recorded 21/10/2011 by User
‘
‘ Keyboard Shortcut: Ctrl+q
‘
Range(“A1:B1”).Select
Selection.Copy
Range(“A3”).Select
Selection.Insert Shift:=xlDown
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

So when you type a rider number in a1, it refreshes the time, you then press ctrl+q and it copies the rider name and time into the row 3, shoving all the other stuff down a row at time

just change the calculation options in Excel Options, stop it from updating automatically

click on the office logo, choose excel options, then choose calculation options and set it to Manual

If we are using macros is there not a cell ‘on-click’ or after update option that can be used to set the adjacent cell to the value of NOW()?

Charlie – are you saying stoner hasn’t just told him how to do it correctly?

Stoner has told him an alternative truth

Thanks fellas. Phone has just gone so I have to do my proper job again! Will have a play with this later.

just change the calculation options in Excel Options, stop it from updating automatically

This is dangerous… if you accidentally hit F9 or whatever it is on your system, all the NOW()s will be updated and you’ll lose your timing information. I never trust Excel and recalculation – sometimes seems to do it on save, load etc. Safer to use the macro method suggested!

🙄

I would either:

1.ring up the Beastway team and ask then if you can use their software
2. ask if our timing team will assist (we use the beastway system)
3. pay £50 for the Pursuit cyclocross timing software
4. continue to try and write your own solution

we would use Pursuit if it could handle staggered starts, IIRC you don’t have that issue. It looks really good with great information on the screens.

HTH, you know our email address 😀

It is is for my own “amusement”. We have competant timers at HtN with all of the necessary laptops and printers. We’re paying them to do it properly.

I was just messing about with the NOW() function on a work matter and my mind wandered.

no problem, just trying to assist

“We’re paying them to do it properly”

we don’t pay anyone (except the MRT “donation”)

probably explains why we are “endearingly shambolic” 😉