stamp time in excel tables

[karim elb]

i am working with tasks in excel table -for example (row 1contains task -to answer i insert row 2 under the task above)-i want to - time stamp in D2
- i hope the answer with formulas -i am beginner in macro
[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]

F
​

[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]task:how to stamp time ....
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]formula?[/TD]
[TD][/TD]
[TD]answer:..........[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

​

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

thankful because i tried and read many ways without
usefulness

 

[mole999]

what would happen so that a time stamp would be generated, explain the actions

 

[karim elb]

<tbody>
[TD="align: left"]I am sory the answer isn't clear and thanks for response -the task is in cell E1 and I need time and date[/TD]

[TD="align: left"]generated in D2 when inserting a row[/TD]

[TD="align: left"]i used a formula in D2 IF(AND(F1<>"Not Done",F1<>""),IF(D2="",NOW(),D2),"") but it appears 1/0/1900 12:00:00 AM[/TD]

[TD="align: left"]I AM USING EXCEL TABLE[/TD]

</tbody>

<tbody>
[TD="class: xl69, align: center"]A[/TD]
[TD="class: xl69, align: center"]B[/TD]
[TD="class: xl69, width: 34, align: center"]C[/TD]
[TD="class: xl69, width: 83, align: center"]D[/TD]
[TD="class: xl69, width: 34, align: center"]E[/TD]
[TD="class: xl69, width: 133, align: center"]F[/TD]
[TD="class: xl69, width: 64, align: center"]G[/TD]

[TD="class: xl68, width: 19, align: right"]1[/TD]
[TD="class: xl68, width: 34"][/TD]
[TD="class: xl68, width: 34"][/TD]
[TD="class: xl68, width: 83"][/TD]
[TD="class: xl68, width: 34"][/TD]
[TD="class: xl68, width: 133, align: left"]

<tbody>
[TD="class: xl67, align: left"]task:how to stamp time in...[/TD]

</tbody>

[/TD]
[TD="class: xl68, width: 64"][/TD]

[TD="class: xl67, width: 19, align: right"]2[/TD]
[TD="class: xl70, width: 34"][/TD]
[TD="class: xl70, width: 34"][/TD]
[TD="class: xl71, width: 83, align: right"]6/26/2016 10:04:00 PM[/TD]
[TD="class: xl70, width: 34"][/TD]
[TD="class: xl72, width: 133, align: left"]answer:..........[/TD]
[TD="class: xl70, width: 64"][/TD]

[TD="class: xl67, width: 19, align: right"]3[/TD]
[TD="class: xl67, width: 34"][/TD]
[TD="class: xl67, width: 34"][/TD]
[TD="class: xl67, width: 83"][/TD]
[TD="class: xl67, width: 34"][/TD]
[TD="class: xl67, width: 133"][/TD]
[TD="class: xl67, width: 64"][/TD]

</tbody>

 

[jtakw]

Hi,

Your formula isn't working because you have a circular reference, you can't reference the cell D2 within the formula in D2, so the correct formula to use would be:

Excel 2010
	D	E	F
1			task:how to stamp time in...
2	6/26/2016 14:35		answer:..........
Sheet1

Cell Formulas
Range	Formula
D2	=IF(AND(F1<>"Not Done",F1<>""),NOW(),"")

HOWEVER, that time stamp will be updated each time the workbook is closed and re-opened, thus giving you an incorrect time stamp.
As far as I know, the only way for that time stamp to remain is using VBA.

 

[karim elb]

hi
okay please what is vba that I can use it

 

[Fishboy]

hi
okay please what is vba that I can use it

Hi karim elb, welcome to the boards.

To do this using VBA you would use a Worksheet_Change event macro. It will be simple enough to explain how to do this, but first I will need you to clarify what cell you want to manually update which will trigger the time stamp. From what I can gather from your earlier posts you will be manually entering the "answer" in cell F2 and you want cell D2 to have the timestamp. If that assumption is correct then this may resolve your issue...

Right-click on the tab for the sheet this is to work on, then select View Code. In the new window that opens, simply copy and past in the following code:

Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Address = "$F$2" And Target.Value <> "" Then
    Range("D2").Value = Date + Time
End If


End Sub

 

[jtakw]

hi
okay please what is vba that I can use it

Looks like you already got an answer.

BTW, welcome to the forum.

 

[karim elb]

hi
i do what mentioned by pasting the code after that i don't now how to complete i read the help and other sites no usefulness
sory

 

[jtakw]

After properly pasting the code in the Worksheet module as described by Fishboy in post #6, there's nothing more you need to do.

When you enter the "Answer" in cell F2, cell D2 will automatically give you the time stamp.

 

[SlamberGamer]

Hi karim elb, welcome to the boards.

To do this using VBA you would use a Worksheet_Change event macro. It will be simple enough to explain how to do this, but first I will need you to clarify what cell you want to manually update which will trigger the time stamp. From what I can gather from your earlier posts you will be manually entering the "answer" in cell F2 and you want cell D2 to have the timestamp. If that assumption is correct then this may resolve your issue...

Right-click on the tab for the sheet this is to work on, then select View Code. In the new window that opens, simply copy and past in the following code:

Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Address = "$F$2" And Target.Value <> "" Then
    Range("D2").Value = Date + Time
End If


End Sub

Hi there thank you very much for the code. it work.. but how can i set more than 1 cell example

Private Sub Worksheet_Change(ByVal Target As Range)




If Target.Address = "$N$5:$N$32" And Target.Value <> "" Then
Range("O5:O32").Value = Date + Time
End If

i want the target address between n5 until n32 and the range is between o5 until 032

thank you very much sir