Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I Time stamp individual rows in excel?
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. |
#2
|
|||
|
|||
"lost and confused" wrote: 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. 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 |
#3
|
|||
|
|||
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 wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I make a time Stamp in excel? | Excel Discussion (Misc queries) | |||
Isolate rows based on highlight (Excel 2003) | Excel Worksheet Functions | |||
How can I delete rows from Pivot Tables in Excel 2000 as in 97 | Excel Discussion (Misc queries) | |||
change excel row height without showing hidden rows | Excel Worksheet Functions | |||
time interval calculations in excel | Excel Discussion (Misc queries) |