how do i set up a time stamp if another number entered in cell?

[freddie74]

Hi i am a novice user of Excel. I am trying to set up a time sheet using
Excel so that as i start/finish a job i can input a figure Say "5" in a cell
and it automatically writes a time stamp in that cell? i have a start cell
and a finish cell so need to set it up for each cell.

 

[David Biddulph]

Control-Semi-colon space Control-Shift-Colon
or otherwise you'll need a VBA solution

IF(A2=5,NOW(),"") will give you a time stamp, but it will update the next
time you update or open the sheet.

 

[Dave Curtis]

Hi Freddie,

How about trying something like this?
Open a blank work book.Right click on the tab and click on View Code.

Type or copy the following into the page that appears:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A:A")) Is Nothing Then
With Target(1, 2)
.Value = Now
.EntireColumn.AutoFit
End With
With Target(1, 3)
.Value = Application.UserName
.EntireColumn.AutoFit
End With
End If
End Sub

Then go back to the worksheet.
If you now type something in A1, the date and time should appear in B1, and
the user ID should appear in C1.
This applies for anything typed in column A.
You may need to format column B to the date/time format you want.

If it's not quite doing what you want, try fiddling around with the VBA and
see what happens.

Dave