Using Now() in a Function

[mark.wolven]

I am using the Now() function within a function, to enter a timestamp
as a spreadsheet is populated. Here's the function:

=IF(AND(LEN(D63)<>0,MOD(B62,12)=0),NOW(),"")

The function works fine, however, the timestamp continues to update,
as I update my spreadsheet. Is there an excel function - either
preexisting within excel, or easily added - to enter a static
timestamp?

 

[Guest]

You can use an Event Macro. Enter the formula just as you have posted it
(say in Z100). Put this in worksheet code:

Private Sub Worksheet_Calculate()
With Range("Z100")
If .Value = "" Then Exit Sub
.Value = .Value
End With
End Sub

As soon as the cell displays the non-blank value of NOW, the macro will
replace the formula by its static value.

 

[Guest]

First click Tools->Options and go to the "Calculation" tab. Check the box
next to "Iteration". This tells Excel to allow circular references.

Now in the cell of interest insert this modified formula, but replace B1
(both of them) with the cell you are entering the formula into.
=IF(AND(LEN(D63)<>0,MOD(B62,12)=0),IF(B1="",NOW(),B1),"")

Format as a date and time.

When you enter the formula in the cell the first time it might come up with
something funny like 1/0/1900 12:00 AM. Just change D63 or B62 so that the
result is "". Once you change them back the date will be right and won't
update.

This may seem complicated but it is the only way I know of without using a
macro. Macros are nice, but they have their downsides. It just depends on
your needs/desires. Hope this helps!!