Non-updating date stamp

[linesite]

Good morning,

I am working on a large database in excel that requires the current date when the word "cleared" or "denied" appears near the end of the worksheet in column AX using this formula.

=IF(OR($AX15="cleared",$AX15="denied"),PERSONAL.XLSB!StaticDate(),"")

I've stored this marco in my personal workbook, but the date continues to recalculate to the current date each time I open the workbook and begin working. I want the date to remain when the word "cleared" or "denied" appeared in column AX without changing to the another date when work begins the next day. What hair I have left I'm about to pull out. Please help.

Function StaticDate()

StaticDate = Now

End Function

Keith

 

[MrKowz]

Keith,

Excel recalculates the workbook every time it is opened, so unless you hardcode the date in a cell, it will change upon opening (or recalculation on a new date).

What I would recommend is use a Worksheet_Change event that monitors for changes in column AX, and if its value is "Cleared" or "Denied", then it adjusts the date.

My question for you is: What column is the date being stored in?

 

[BeingYogi]

May Be:

Function StaticDate()
StaticDate = Now()
End Function

 

[linesite]

Column D

 

[MrKowz]

Place this code in your workbook at the worksheet level (right click on the sheet tab you want this to run on, and click "View Code")

This will monitor for changes in Col AX, and if it says "Cleared" or "Denied", it will change the value in Col D.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("AX:AX")) Is Nothing Then
    If Target.Value = "Changed" Or Target.Value = "Denied" Then
        Range("D" & Target.Row).Value = Now
    End If
End If
End Sub

 

[linesite]

This private sub works great! Thank you very much.

 

[MrKowz]

This private sub works great! Thank you very much.

Not a problem. Thank you for the feedback.

 

[linesite]

This private sub routine works great and functions well--thanks to MrKows and his expertise. Is it possible to create another private sub routine to monitor changes to another column within the same worksheet? I have data in column A by default contains "-" in each cell. Data validation list provides the user with several choices (i.e. 50-Andrews, 52-New Mexico, 68-Odessa, etc.). When the user selects one of these choices described previously a static non-changing date appears in column C. However, each time I open the worksheet from day to day the date in column C changes to the current date. Like the first post on this thread I'm aiming for or hoping for a static hard date in column C to remain unchanged after its first calculation.

 

[linesite]

This private sub routine works great and functions well--thanks to MrKows and his expertise. Is it possible to create another private sub routine to monitor changes to another column within the same worksheet? I have data in column A by default contains "-" in each cell. Data validation list provides the user with several choices (i.e. 50-Andrews, 52-New Mexico, 68-Odessa, etc.). When the user selects one of these choices described previously a static non-changing date appears in column C. However, each time I open the worksheet from day to day the date in column C changes to the current date. Like the first post on this thread I'm aiming for or hoping for a static hard date in column C to remain unchanged after its first calculation.

Keith
linesite

 

[nawdrizzy]

Hello,

Can you adjust this code so that it monitor the changes in columns D, E, J, K, and return Now() in columns D and E?

Thank You