Stop counting days

[kweisser]

I have a formula to count number of days that I would then like to stop once another cell is populated with a date. I have the following formula that works for the counting, but not sure how to add the additional steps to stop once the neighboring cell (J2) is populated. Any help is most appreciated.

IF(AND(H2="",I2=""),"",IF(H2>I2,(H2+7)-TODAY(),(I2+7)-TODAY()))<o></o>

 

[mrmmickle1]

You may want to look at a Worksheet_Change Event. For example when cell J2 is populated it will put a time stamp in the adjacent cell.

Try putting this code in the worksheet module of the worksheet your data is on:

[COLOR=#0000ff]Private Sub [/COLOR]Worksheet_Change([COLOR=#0000ff]ByVal [/COLOR]Target[COLOR=#0000ff] As [/COLOR]Excel.Range)
    [COLOR=#0000ff]If [/COLOR]Range("J2") <> "" [COLOR=#0000ff]Then[/COLOR]
         Range("I2") = Format(Now(), "m.d.yy h:mm AM/PM")
[COLOR=#0000ff]    End If[/COLOR]
[COLOR=#0000ff]
[/COLOR]
[COLOR=#0000ff]End Sub[/COLOR]

It will timestamp cell i2 if cell j2 is not blank ("")

 

[mrmmickle1]

This may work better. It will only change the timestamp value if cell j2 is modified. Otherwise the timestamp value may change every time a change is made to the worksheet:

[COLOR=#0000ff]Private Sub [/COLOR]Worksheet_Change([COLOR=#0000ff]ByVal[/COLOR] Target [COLOR=#0000ff]As [/COLOR]Excel.Range)
  [COLOR=#0000ff]  If [/COLOR]Target.Column = 10 [COLOR=#0000ff]And [/COLOR]Target.Row = 2 [COLOR=#0000ff]Then[/COLOR]
   [COLOR=#0000ff]    If [/COLOR]Range("J2") <> "" [COLOR=#0000ff]Then[/COLOR]
            Range("I2") = Format(Now(), "m.d.yy. h:mm AM/PM")
[COLOR=#0000ff]        End If[/COLOR]
[COLOR=#0000ff]    End If[/COLOR]
[COLOR=#0000ff]End Sub[/COLOR]

There may be a better way to go about this with a formula. Not too sure though...