Fixed/non volatile date function

[louww]

The aim is to place a date in a cell, which is dependent on antoher cell showing the value- 100% (manuallly) and then to fix that date permanently.

The problem is that this date keeps on updating when the spread sheet is opened on a new day.

I'm using this formula and function

=IF(G81>99%,fixeddate(),"")

Option Explicit
Function FixedDate()
FixedDate = Date
End Function

Any help will be appreciated

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$G$81" Then

If Target.Value > 0.99 Then

Me.Range("H81").Value = Date
End If
End If
End Sub

[louww]

Thanks, this formula has to be copied down to several cells, can I change the target.adress to a range e.g. G81:G134?
Or does a range require something else?

[CodeNinja]

good article on exactly this kind of thing: http://chandoo.org/wp/2009/01/08/timestamps-excel-formula-help/

resolution would be:
1- turn circular references on
2- change formula fixeddate to:
Function fixeddate() As Long
fixeddate = Now
End Function
3- put in cell formula of =IF(G81>0.99, IF(H81="",fixeddate(),H81),"") assuming cell H81 is dependant on G81.

Note: This will change the cell to "" if you lower the number below .99 If you want that part removed, you will need to remove the final ""

[louww]

Thanks this will help!

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Me.Range("G81:G134")) Is Nothing Then

If Target.Value > 0.99 Then

Target.Offset(0, 1).Value = Date
End If
End If
End Sub

[CodeNinja]

XLD,
Is there a performance difference between the circular reference solution and your worksheet change solution, and what would that performance difference be?

[mikerickson]

Perhaps this UDF will work for you.

Function LockDate(Refresh As Boolean) As Date
If Refresh Then
LockDate = Date
Else
LockDate = Application.Caller.Text
If IsDate(LockDate) Then
LockDate = DateValue(LockDate)
ElseIf IsNumeric(LockDate) Then
LockDate = Val(LockDate)
End If
End If
End Function

If B1 holds the formula =LockDate(A1=1),
changing A1 to 1 will put the current date as the value of that cell.
change A1 to 2 and the value will not change. Day after day..it will not change, until the user puts 1 in A1, at which time the value will be that day.

[Bob Phillips]

Any difference will be negligible and not noticeable, but allowing circular references is not a good idea in my view, it might hide a problem.

[louww]

thanks everyone, these are great solutions
I agree with XLD, I'm not a fan of circular references