I have the following "timestamp" that says if there's an entry in a D cell, the adjacent E cell will get a timestamp.
Here's the code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("D2:D5001"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 1).ClearContents
Else
With .Offset(0, 1)
.NumberFormat = "ddd mmm dd, yy hh:mm"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With
End Sub
Now... in the F cell, I have the following formula that counts how many days have past since the timestamp.
Here's the formula: =" Day " & DATEDIF(E2,TODAY(),"d")
Dilemna #1:
If entry in cell D triggers a timestamp in E... where F then starts to count how many days has passed since the timestamp in E... what code/formula/magic can I use when I enter something in G to stop cell F from counting anymore days.
Here's a different way to look at it. I place sardines in a frying pan (D) which timestamps when I did it (E). (F) starts to count how many days have passed since I left the sardines in the pan. My house starts to stink... and I decide to stop the experiment (G) by entering STOP in the G cell.
Phew! I hope this helps... and the sardines are making me hungry.
Dilemna #2:
How do I stop users from erasing the formula in cell F:
=" Day " & DATEDIF(E2,TODAY(),"d")
Thanks again.
Bookmarks