date stamp to keep date

[wpreqq99]

When user types in the letter Y in Column P, I want the next cell, in
Column Q to show the current date. I want that date to stay, and not
change tomorrow. So, the NOW function doesn't work for me.

I looked in here yesterday, and found what I thought was the answer. I
put this in a module.

Function DateAndTime()
DateAndTime = Now
End Function

When I tested it yesterday, it seemed to work ok. Of course I couldn't
tell until today, when the system clock changed. Today, I see the date
of 4/1 didn't hold. It now says 4/2.

Is there something else I can try? I can't change the system clock in
order to test it all out. I have to wait until the next day.
Please help!!
Thanks
j.o.

 

[Mike H]

Hi,

Right click your sheet tab, view code and paste this in and you get a static
date in column q if Y is entered in col P

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 16 Or Target.Cells.Count > 1 _
Or IsEmpty(Target) Then Exit Sub
If UCase(Target.Value) = "Y" Then
Target.Offset(, 1).Value = Date
End If
End Sub

Mike

 

[wpreqq99]

When user types in the letter Y in Column P, I want the next cell, in
Column Q to show the current date. I want that date to stay, and not
change tomorrow. So, the NOW function doesn't work for me.

I looked in here yesterday, and found what I thought was the answer. I
put this in a module.

Function DateAndTime()
DateAndTime = Now
End Function

When I tested it yesterday, it seemed to work ok. Of course I couldn't
tell until today, when the system clock changed. Today, I see the date
of 4/1 didn't hold. It now says 4/2.

Is there something else I can try?  I can't change the system clock in
order to test it all out. I have to wait until the next day.
Please help!!
Thanks
j.o.

OH, I forgot..... this range in Column Q is protected..... in case
this makes a difference.