Time stamp on cell entries

[fabio]

Hi

Is it possible to record a time stamp of when an entry is made in a cell. I
want to put together a transcription test whereby a candidate would have to
type into a column a number of character strings from a typed sheet. I would
like to be able to record how long the data transcription takes.

Thanks

G

 

[Joel]

You could use a worksheet change macro and put the time into the comment of
the cell.

Sub worksheet_change(ByVal target As Range)
'
If target.Comment Is Nothing Then
target.AddComment
target.Comment.Visible = False
End If
target.Comment.Text Text:=Format(Now, "DD/MM/YY HH:MM:SS")
End Sub

 

[Dude3966]

Strictly speaking the date stamp in Excel is done py pressing Ctrl + ;
however if you would like to place a date stamp automatically when data is
entered into a specific cell the i find that the following formula works well.

=NOW()

This will place the current date and time e.g. 15/08/2008 13:57. The down
side is that every time the sheet recalculates i.e. any time enter or return
is pressed the date stamp is updated. The easyest way around this is to
create a circular statment. For example if when data is entered in to cell A1
you want to place a date stamp in B1 then the following formula will work.

=IF(A1="","",IF(B1="",NOW(),B1))

However due to the circular reference which basically stop the cell
recalculating an error will be returned. To stop this go to Tools>Optoion
select the Calculation tab and tick the Itterations box. This will solve the
circular refernce problem.