Excel question

  interzone55 20:31 18 Dec 2003

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


  VoG II 22:24 18 Dec 2003

If you type into a cell


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 2003

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 2003

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


  interzone55 20:29 22 Dec 2003

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.


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

Elsewhere on IDG sites

Galaxy Note 8 vs iPhone X

The secrets of creating gory VFX

How to update iOS on iPhone or iPad

WhatsApp : comment lire vos messages sans que l’expéditeur le sache