Fixing a Date in Excel 1

[JHHPavel]

I'm using Excel to track volumes on various medical procedures. When the volume reaches or passes a certain level, let's use 100 as the example, I want to enter that date in an adjacent cell. I can do this by the formula below where the volume is entered into cell B6:

=IF(B6>100,NOW()," ")

The problem is that everytime I open the spreadsheet, the value will be updated to the current date, rather than the date on which that test passed the volume requirement of 100. Is there any automatic way to fix the date so that once a date is entered, it is never changed?

 

[SkipVought]

Hi,

assuming that your FORMULA is in C6...
[tt]
=IF(B6>100,IF(C6<>"",NOW(),C6),"")
[/tt]
and allow circular reference.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]