fixing the result of a formula after a certain date
 

Hello everyone,

I want the result of a formula to be changed into a fixed value after a
certain date has passed. Does anyone know how to do that?

fixing the result of a formula after a certain date
 

Hi Rein,

A formula cannot convert itself to no formula, but you can
test the date and have the formula return a specific value..

=IF(TODAY()Date(2005,12,31),100, A1*b1)
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Rein" wrote in message ...
Hello everyone,

I want the result of a formula to be changed into a fixed value after a
certain date has passed. Does anyone know how to do that?

fixing the result of a formula after a certain date
 

You will need to copy it and paste (into the same cell) as values - the
tricky part is how to trigger it - i.e. when do you test for the date and
make the change? And how do you know what the date is when you should do the
change?

Assuming you know (or can calculate) the date when you want it done, you
probably would want code to go in the Workbook_Open event, so it triggers the
first time the book is opened on or after the date specified:

Private Sub Workbook_Open()
Dim MyDateCell as Range, SpecifiedDate as Date

' Set MyDateCell to the cell with the date formula in it
' Calculate (or set) SpecifiedDate to be the effective date for the change

If Date() = SpecifiedDate Then
MyDateCell.Copy
MyDateCell.PasteSpecial xlPasteValues
End If

End Sub

"Rein" wrote:

Hello everyone,

I want the result of a formula to be changed into a fixed value after a
certain date has passed. Does anyone know how to do that?