How do I Time stamp individual rows in excel?

L

lost and confused

I have to use excel to update inventory. I don't know what formula to use.
What I need to have is every time a record is added, a seperate column will
put a time stamp there. I tried useing the NOW() command, but all the
records changed when I added data in a row. Please help.
 
P

PeterAtherton

lost and confused said:
I have to use excel to update inventory. I don't know what formula to use.
What I need to have is every time a record is added, a seperate column will
put a time stamp there. I tried useing the NOW() command, but all the
records changed when I added data in a row. Please help.
Click to expand...

The following function will not recalculate unless you press ALT + F9

Function DateStamp() As Date
Static mydate
mydate = Int(Now)
DateStamp = mydate
End Function

Copy this into a VB Module (ALT + F11, Insert Module) in the workbook and
type =datestamp() into a cell.

Alternatively, enter the dates as text using this macro.

Sub test()
Dim c
For Each c In Selection
c.Value = Int(Now)
c.NumberFormat = "dd/mm/yy"
Next
End Sub

copy into a vb module as above select the range to enter the dates and run
the macro from Tools, Macro select test and click run.

If you are happy with this you can create a button or drawing object and
assign the macro to the button.

Regards
Peter
 
G

Gord Dibben

lost

You could use event code to enter the date which would remain static.

Below are a couple of sets of event code. Use whichever suits you.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Col B time will not change if data in Col A is edited
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
n = Target.Row
If Excel.Range("A" & n).Value <> "" _
And Excel.Range("B" & n).Value = "" Then
Excel.Range("B" & n).Value = Format(Now, "hh:mm:ss")
End If
End If
enditall:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
'Col B time will change if data in Col A is edited
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
With Target
If .Value <> "" Then
.Offset(0, 1).Value = Format(Now, "hh:mm:ss")
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

To use this code, right-click on the sheet tab and "View Code". Copy/paste
the set of code you want to the module.


Gord Dibben Excel MVP


On Sun, 11 Sep 2005 13:23:01 -0700, "lost and confused" <lost and
 
Top