Stop counting days

kweisser

New Member
Joined
Jul 7, 2008
Messages
17
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:p></o:p>
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

mrmmickle1

Well-known Member
Joined
May 11, 2012
Messages
2,456
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:
Code:
[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

Well-known Member
Joined
May 11, 2012
Messages
2,456
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:

Code:
[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...
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,509
Messages
5,523,312
Members
409,511
Latest member
hitesh222002

This Week's Hot Topics

Top