Record dates based on criteria

[HappyDaddy]

How do I record the date when a specific cell reaches a specific value?

ie:
Cell "A1" increases in value based on user input. Starts at zero on day one
and is added to over a period of time.

I would like to record the date when cell "A1" first exceeds a predetermined
value of say...100

I would then like to record the date when it excedds another value of say
....327

 

[Gary''s Student]

Does cell A1 conain a formula or an inputted value??

 

[HappyDaddy]

Cell A1 contains a simple addition formula based on two cells that are
manually input

 

[Dave]

Hi,
Perhaps something like:

=IF(A1>100,TODAY(),"") in one cell
=IF(A1>327,TODAY(),"") in another

Regards - Dave.

 

[Dave]

Oh yeah, D'uh!
Sorry about that. I guess you'd need an event macro.
Dave.

 

[Gary''s Student]

Then install this worksheet event macro:

Private Sub Worksheet_Calculate()
Set a1 = Range("A1")
If Not IsEmpty(a1.Offset(0, 1).Value) Then Exit Sub
If a1.Value < 101 Then Exit Sub
Application.EnableEvents = False
a1.Offset(0, 1).Value = Date
Application.EnableEvents = True
End Sub


Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

 

[HappyDaddy]

Thanks so much guys, worked great

Then install this worksheet event macro:

Private Sub Worksheet_Calculate()
Set a1 = Range("A1")
If Not IsEmpty(a1.Offset(0, 1).Value) Then Exit Sub
If a1.Value < 101 Then Exit Sub
Application.EnableEvents = False
a1.Offset(0, 1).Value = Date
Application.EnableEvents = True
End Sub


Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm