Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Record dates based on criteria
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Record dates based on criteria
Does cell A1 conain a formula or an inputted value??
-- Gary''s Student - gsnu200798 "HappyDaddy" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Record dates based on criteria
Cell A1 contains a simple addition formula based on two cells that are
manually input "Gary''s Student" wrote: Does cell A1 conain a formula or an inputted value?? -- Gary''s Student - gsnu200798 "HappyDaddy" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Record dates based on criteria
Hi,
Perhaps something like: =IF(A1100,TODAY(),"") in one cell =IF(A1327,TODAY(),"") in another Regards - Dave. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Record dates based on criteria
Which is fine until tomorrow.
-- David Biddulph "Dave" wrote in message ... Hi, Perhaps something like: =IF(A1100,TODAY(),"") in one cell =IF(A1327,TODAY(),"") in another Regards - Dave. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Record dates based on criteria
Oh yeah, D'uh!
Sorry about that. I guess you'd need an event macro. Dave. "David Biddulph" wrote: Which is fine until tomorrow. -- David Biddulph "Dave" wrote in message ... Hi, Perhaps something like: =IF(A1100,TODAY(),"") in one cell =IF(A1327,TODAY(),"") in another Regards - Dave. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Record dates based on criteria
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 -- Gary''s Student - gsnu200798 "HappyDaddy" wrote: Cell A1 contains a simple addition formula based on two cells that are manually input "Gary''s Student" wrote: Does cell A1 conain a formula or an inputted value?? -- Gary''s Student - gsnu200798 "HappyDaddy" wrote: 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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Record dates based on criteria
Thanks so much guys, worked great
"Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200798 "HappyDaddy" wrote: Cell A1 contains a simple addition formula based on two cells that are manually input "Gary''s Student" wrote: Does cell A1 conain a formula or an inputted value?? -- Gary''s Student - gsnu200798 "HappyDaddy" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Show dates based on criteria | Excel Worksheet Functions | |||
Counting dates based on certain criteria | Excel Worksheet Functions | |||
Counting unique dates based on selected criteria in a list | Excel Worksheet Functions | |||
columns to rows based on record criteria | Excel Worksheet Functions | |||
Criteria Lookup based on Dates | Excel Discussion (Misc queries) |