Non Volatile Time Stamp function

bearcub

Well-known Member
Joined
May 18, 2005
Messages
625
Office Version
  1. 365
  2. 2013
  3. 2010
  4. 2007
Platform
  1. Windows
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:

Code:
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
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

bearcub

Well-known Member
Joined
May 18, 2005
Messages
625
Office Version
  1. 365
  2. 2013
  3. 2010
  4. 2007
Platform
  1. Windows
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

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,125
Messages
5,599,856
Members
414,342
Latest member
K Darrell Smith

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top