at the moment you enter the cell it will be formated to give the time of that
moment.
thanx for all the great answers.

bee

i need to add that , i need it to be formated in such a way that once u leave
the cell it will be locked and would not change if u return to it again at a
different time. Please write the syntax.
best regards

"kbee" wrote:

at the moment you enter the cell it will be formated to give the time of that
moment.
thanx for all the great answers.

bee

This could be done, but not with a worksheet formula. It would need to be
done using VBA code.

To accomplish the second part of your request, the worksheet would need to
be protected and the cells where you are entering the date would have to be
locked. Cells where you do want people to be able to type in/edit
information would need to be unlocked.

Without more specific information I can't do much more than show 'generic'
code on how to do this. This code would put the date/time into any empty
cell in column A that is clicked on and keep that from being changed, while
it would ignore changes in any other column.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const sheetPW = "jlatham" ' the assigned password for the sheet
If Application.Intersect(Target, Range("A:A")) Is Nothing Then
Exit Sub ' did not click cell in column A
End If
If Target.Cells.Count 1 Then
'chose more than 1 cell in column A
Exit Sub
End If
'does the cell have anything in it already?
If Not IsEmpty(Target) Then
'something there, do not change it
Exit Sub
End If
'unprotect the sheet so that
'we can put a time in the cell
ActiveSheet.Unprotect Password:=sheetPW
Target = Now() ' insert the date/time
'protect the sheet to keep from changing time entries
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, _
Scenarios:=True, Password:=sheetPW
End Sub

"kbee" wrote:

at the moment you enter the cell it will be formated to give the time of that
moment.
thanx for all the great answers.

bee