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?

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.

Thanks for your help. I used the code you wrote and it worked great. Thanks.

Ask Your Question

Weekly Poll