Excel question

  interzone55 20:31 18 Dec 03
Locked

I need to set up log in excel and need to time stamp each entry, so that when something is typed in A1, the time and date are entered into A2. IF(A1="","",NOW()) does the job, but updates the time eachtime the sheet is saved, I need the time and date to be static.

I'm sure I've seen this answered somewhere

TIA
Alan

  VoG II 22:24 18 Dec 03

If you type into a cell

=NOW()

then press F9 then press Enter it will enter the current time & date. You need to format the cell accordingly.

If you want this to happen automatically it can be done using a macro that uses the WorkSheet_Change property. We would need to know the name of the sheet and which row or column entry has to trigger this.

  VoG II 22:42 18 Dec 03

Right click the worksheet tab and select View Code

Paste in:

Private Sub worksheet_change(ByVal target As Excel.Range)

If target.Address(False, False) = "A1" Then
Range("A2") = Now()

End If

End Sub

As usual VoG's contribution beats me to it so I can only offer an alternative (almost).

Among other things the following code locks the time by checking to see whether the B Column already contains a number. If you do not wish to lock the number then VoG's solution is the best.

The target is the cell that you have just left and the code assumes that all of the triggers are in Row 1 with the timestamp in Row 2

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

Private Sub Worksheet_Change(ByVal Target As Range)

On Error Resume Next

If Target.Row <> 1 Then Exit Sub

If Not Application.IsNumber(Target.Offset(1, 0)) Then Target.Offset(1, 0) = 0

If Target.Offset(1, 0) > 0 Or Target.Value = "" Then Exit Sub

Target.Offset(1, 0) = Now()

End Sub

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

If you wish the triggers to be in column A and the timestamp in B then substitute the word Column for Row and change the (1, 0) entries to (0, 1)

Best wishes

Oh for an edit button FE! OR a Code facility to post code as it is written

This line should be on a seperate line of the code

Target.Offset(1, 0) = Now()

  interzone55 22:11 19 Dec 03

Thanks everyone, I shall try them all when I get back to work on Monday and see which offers the most workable solution.

Alan

  interzone55 20:29 22 Dec 03

The requirements were a little more complicated that I understood, so I'm going with an access database, so I have to ponder over christmas, thanks to all who responded.

Alan

This thread is now locked and can not be replied to.

Amazon Fire HD 8 review: A brilliant combination of function and value – with one massive caveat

1995-2015: How technology has changed the world in 20 years

How to create an introvert-friendly workplace

Apple Watch Series 2 review | Apple Watch 2 review: New Apple Watch is faster, brighter,…