Current time for data entered in column

M

mohitmahajan

Whenever data is entered in a cell, current time should come auto i
another corresponding cell. For example, time should automaticall
come in Y column for any data entered in any cell of the column A. Fo
A10 time should come in Y10 and for A12 it should come in Y12 and s
on. I need it for doing time and motion study. Appreciate all help
 
N

Norman Harker

Hi Mohitmahajan!

Based on JE McGimpsey (http://www.mcgimpsey.com/excel/timestamp.html)

Let's say that every time an entry is made in cells A2:A100, the
corresponding cell in column Y should have the date and time entered.
You could use this Worksheet_Change() macro - put it in the worksheet
code module:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

With Target

If .Count > 1 Then Exit Sub

If Not Intersect(Range("A2:A100"), .Cells) Is Nothing Then

Application.EnableEvents = False

If IsEmpty(.Value) Then

.Offset(0, 1).ClearContents

Else

With .Offset(0, 24)

.NumberFormat = "dd mmm yyyy hh:mm:ss"

.Value = Now

End With

End If

Application.EnableEvents = True

End If

End With

End Sub

Note that this will clear the date-time when the target cell is
cleared. Remove the "If IsEmpty(...)...End If" control structure if
that's not desired.

You can, of course use any valid date/time format.


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
M

mohitmahajan

Thanks Norman for the prompt help. I tried using this but was los
since am not good with macros and VB - practically 0 in these.

Pls let me know how to use this/record this. Let me know step by step
this will be big help
 
M

Max

Just to add a little to one of the steps
outlined nicely by Frank (just in case .. )

Before you proceed to:
- paste the code in the appearing VBA editor window
Click to expand...

do clear/delete the "defaults" appearing in the window,
i.e. delete the lines below completely first:

Private Sub Worksheet_SelectionChange(ByVal Target As
Excel.Range)

End Sub
 
M

mohitmahajan

Thanks a lot for all your help, it works and has made my work easier.
Have a great day :O
 
Top