Non Volatile Time Stamp function

[bearcub]

I have a spreadsheet where I need to track the time a date was entered in two different tables. I would like it remain static. I've been searching the web for something like this but I see either a formula in the worksheet change event or formulas inserting the Now function. I don't want to use the NOW() function since the time will change whenever I update the spreadsheet and the time changes.

I need something which behaves like the ctl-sht-; keys - not as a macro but as a function.

How would you convert the traditional sheet change event approach:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range
    Set r = Target
    If Intersect(Range("K:K"), r) Is Nothing Then Exit Sub
    If r.Value <> 1 Then Exit Sub
    If r.Offset(0, 1).Value <> "" Then Exit Sub
    Application.EnableEvents = False
        r.Offset(0, 1) = Date
    Application.EnableEvents = True
End Sub
[code/]

I will be using this function in different areas of the worksheet so I would need to have a function that I could enter into a cell which points to the cell adjacent to it?

Is this possible?

Thank you for your help in advance,

Michael

 

[bearcub]

I did find this function from the message board dated 2006 which is non volatile:

https://www.excelforum.com/excel-programming-vba-macros/555688-static-time-date-stamp.html

Public Function DATESTAMP(WatchedCell As Range) As Variant
If Len(WatchedCell.Value) Then
DATESTAMP = Now()
Else
DATESTAMP = ""
End If
End Function
[code/]

Now, I have another issue. My workweek  starts 2PM pm Thursday and runs through to the following Thursday tile 1:59:59 the following Thursday (my week spans 8 days but because I'm starting at 2PM I need to go into the following Thursday). I would like to create which would allow the user if the entry was 2:00PM the following week.  I'm trying to track my activities on a weekly basis and I don't it to spill into the following week if it is later than 1:59:59 on the 8th day of the week.

Thank you for your help.

I was thinking about using one of the time functions but I wasn't sure if one of these functions would recognize the value entered in a cell.

Michael