Stopping the clock

[colin5392]

I have created a Risk Register, which lists the number of days a risk has been open for, using the formula below (Column AC), where G4 is the date the risk was identified / raised on the register.

=IF(AA4="Open", TODAY()-G4, "")

Column G - Date Originated
Column AA - status, either Open or Closed
Column AC - Days Open

BUT

When the Risk has been dealt with and the entry can be closed, instead of clearing out the data in Column AC (Days Open), I would like the current value to remain as that value, e.g. if the entry was considered Closed in Column AA, the value would become a static value, (no longer changing).

I am assuming that the above formula would have to be completely rewritten but I don't know how to set it as a true value, where an entry is Closed.

Can you advise?

 

[colin5392]

Any advice please?

 

[shg]

You could do that with iterative calculation enabled, but I'd do it like this:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Originated​

[/td][td="bgcolor:#F3F3F3"]

Closed​

[/td][td="bgcolor:#F3F3F3"]

Days Open​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

16-May-2018​

[/td][td]

04-Jun-2018​

[/td][td="bgcolor:#CCFFCC"]

19​

[/td][td="bgcolor:#CCFFCC"]C2: =IF(B2="", TODAY(), B2) - A2[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

01-Jun-2018​

[/td][td][/td][td="bgcolor:#CCFFCC"]

10​

[/td][td][/td][/tr]
[/table]

 

[Eric W]

Another way is to use an event handler. Try this:

Open a copy of your workbook. On the sheet with your data, right click on the sheet tab on the bottom and select View Code. Paste the following code into the sheet that opens:

Private Sub Worksheet_Change(ByVal target As Range)

    If Intersect(target, Range("AA:AA")) Is Nothing Then Exit Sub
    If target.Rows.Count > 1 Then Exit Sub
    If LCase(target) = "closed" Then Cells(target.Row, "AC").Value = Date - Cells(target.Row, "G")
    
End Sub

Press Alt-Q to close the editor. Now when you type "Closed" in column AA, this macro will detect it, and change the value in AC to a static value.

Hope this works for you!

 

[colin5392]

Thanks - that's great.

One additional query, if i were to add a status column, how would the formula change to work off both the 'status' of Closed AND the date the entry was closed (in the above example -

A B C D
1 Originated Status Date Closed Days Open
2 16 May 18 Closed 4 Jun 18 =IF(C2="", TODAY(), C2-A2)
3 01 Jun 18 Open =IF(C3="", TODAY(), C3-A3)

So, if B = Closed AND C contains a date, what would I need to use?

 

[shg]

That would be redundant, no? What does it mean if it says "closed", and there's no date? Or vice versa?