Formula to stop counting if a value is met.

[spinmaniac2]

Hi all, I am having issues with a nested formula (unless someone had a better idea...lol) here is what I have

A1 - Date (this is manually entered)
B1 - Age (in days) using the following formula - =IF(ISBLANK(A2),"",TODAY()-A2)
C1 - Status - this is manually entered - "Completed", "in progress" etc.

What I am trying to figure out is how to get the formula in B1 to stop counting, and retain the last value, if C1 states "completed"

Any ideas?

Thanks so much.

 

[Joe4]

Welcome to the Board!

This requires VBA. So you can change the value in B1 from a formula to a hard-coded value so that it does not change when C1 is set to "Completed".

I am not sure if you want this to happen on just row 1, or all of column C. I am assuming all of column C.

Here is the code that you need:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Only run when a single cell is manually updated
    If Target.Count > 1 Then Exit Sub
    
'   Only run when column C is updated
    If Target.Column = 3 And LCase(Target.Value) = "completed" Then
        Application.EnableEvents = False
        Target.Offset(0, -1).Value = Target.Offset(0, -1).Value
        Application.EnableEvents = True
    End If

End Sub

To put it in the proper place, right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste the code into the resulting VB Editor window. It will run automatically when any value in column C is updated to "completed".

 

[spinmaniac2]

That did it...thanks so much!!

 

[Joe4]

You are welcome!