Can I stop date counter when I fill a cell

May 10, 2011 at 15:40:09
Specs: Windows XP
I am trying to figure out how long an insurance policy is in underwriting.
So far this is what I have come up with:
Column B = submit date
Column C = formula =NETWORKDAYS(B30,NOW()) with color coded conditional formatting for days in underwriting
Column D = completion date
Column E = formula =IF(D30=",",NETWORKDAYSB30,D30))
When I enter a date in column D, I would like column C to stop counting. I would also like to average the days in column E but after a policy is through underwriting I would like to hide or remove the line that the policy is on but keep the days in the average.
Can I do all this?

See More: Can I stop date counter when I fill a cell

Report •

May 10, 2011 at 17:24:42
Functions such as NOW() and TODAY() are known as Volatile functions, meaning that they are going to calculate every time the sheet calculates.

Since you can't stop those functions from updating, you need to use VBA to replace the formula with a constant (e.g. the current result of the formula) based on an "event", such as entering data in a specific row, column, cell, etc.

For example,

If you have one of your volatile functions in Column C, and enter something (anything) in Column D this code will lock in the current value in Column C on the same row as the "something" you just entered.

This will happen automatically and the value will never change again since the formula will be replaced with the latest result.

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Cells.Count = 1 Then
'Run only when change is made in Column D
  If Target.Column = 4 Then
   Application.EnableEvents = False
'Replace the formula with the current result
     Range("C" & Target.Row) = Range("C" & Target.Row)
   Application.EnableEvents = True
  End If
 End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

May 11, 2011 at 08:48:56
Thanks for your help. I used the code you wrote and it worked great. Thanks.

Report •

Related Solutions

Ask Question