I will be giving a survey using Microsoft Excel to administer it.  A
person taking the survey will do so in an Excel worksheet.  I want to
time how long each person takes to complete the survey.  So I need a
way for Excel to record the exact time a cell is filled and the person
hits "return".  Excel has a worksheet function that is very close to
what I need, but the way Microsoft set how it works makes it
completely useless.

The function is called "Now" with syntax "=now()".  When you type that
in a cell and hit "return", the cell will record the exact time you
hit return off the system clock.  You can embed a "Now" command in an
"If" statement and set it to record the time when a specific cell is
filled and the person hits "Return".  I could have one of these
statements set for the first question in the survey and one for the
last question in the survey and record the time the first question was
answered and another when the last question is answered and get
elapsed time, that is if "Now" was not useless.

The reason "Now" is useless is, if you have several "Now" commands in
your worksheet, all of them are reactivated every time one of them is
activated.  In other words, if I have a "Now" command for the first
question in the survey, and another for the last question in the
survey, the one for the first question will be set when they answer
that question, and then will be reset to the current time on the
system clock when the "Now" command for the last question in the
survey is answered.  They both will show the system clock time when
the last question was answered.  There is no way to get elapsed time,
so the "Now" command in Microsoft Excel is useless if you want to
record more than one time.

Is there a way to record the system time in a cell, and leave at that
value when a later system time is recorded in a different cell?  If
not is there any way for me to get elapsed time?

Frisbeeman,

You can use the SelectionChange event of the Worksheet to take action
when moving between cells. I don't know exactly what logic you need,
but try something like this in the Module for your Worksheet. I have
arbitrarily decided to care about cells A1 and B1. If either of these
cells are entered, a flag gets set. When I leave the cell, Excel will
timestamp A2 or B2, respectively.

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

Option Explicit
Public flag As Integer

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ' See if we are leaving any of our tracked
    ' cells. If so, and a value has been entered,
    ' timestamp the matching timestamp cell.
    If flag = 1 Then
        If Range("A1").Value <> "" Then
            Range("A2").Value = Now()
        End If
        flag = 0
    End If
    If flag = 2 Then
        If Range("B1").Value <> "" Then
            Range("B2").Value = Now()
        End If
        flag = 0
    End If
    
    ' Set the flag if entering a cell that
    ' we want to track and the tracked cell
    ' has not yet been filled in.
    If Target.Address = "$A$1" Then
        If Target.Value = "" Then
            flag = 1
        End If
    ElseIf Target.Address = "$B$1" Then
        If Target.Value = "" Then
            flag = 2
        End If
    Else
        flag = 0
    End If
End Sub

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

Additional Resources:
Look in Excel Help under SelectionChange.

Please let me know if you need clarification.

Good luck with your Excel project!

- Hammer

The problem is not the implementation of now() so much that everytime
anything changes, ALL values are recalculated, so of course, now
changes.  I thought maybe protecting cells could get around it, but
no.
Here the best work around I can think of quickly:
Create two macros called start and stop.  Assign them to convenient
F-keys and get your person to use them.
Start does the following:
Enters the formula "=now()" into some cell (say A1) -- now you have
the time at the start
Copies this value
Does a "paste special/values" into another cell (say A2).

Stop does much the same - takes and freezes the value of the time
*then*.

Then you just have another cell that takes on the value of the
difference between the frozen values.

Actually, it is a bit simpler than that!
Just have some cell somewhere that has "=now()" as its formula.

The start macro just forces a recalculate, copies this value, and does
the paste special.
The stop macro doest the same and pastes the value into another cell.

If only there was some way of causing the data entry to trigger the
macros ... hmmmm.

Hi,
Refer the following link which gives solution  for the similar problem
using formula Choose function.
http://answers.google.com/answers/main?cmd=threadview&id=76741

Hope this helps.

RAC