Posted: 7/27/2011 8:14:46 AM EDT
|
OK, here's what I'm trying to do.
I have one sheet with a bunch of dates on it, and on another sheet I need to add the number of dates together that fall within a certain range. So basically if I have 20 dates, 10 of those dates are a year or older than today, I need it to say 10... I guess I'm not sure how to reference a different sheet than your on, and how to only look for a certain date range and then add the number of cells together that those dates fall within a certain amount of time from the current date. |
|
To identify your dates that are older then today add a new column. =if("your date cell" <now(),1,0) and copy that next to each date. Now you have a 1 next to every date before today. Sum that column and you'll have the count.
A reference to another sheet looks like =sheet2!A1. I prefer to just start typing my formula and then going to the other sheet and clicking on the cells I want. |
|
Quoted:
OK, here's what I'm trying to do. I have one sheet with a bunch of dates on it, and on another sheet I need to add the number of dates together that fall within a certain range. So basically if I have 20 dates, 10 of those dates are a year or older than today, I need it to say 10... I guess I'm not sure how to reference a different sheet than your on, and how to only look for a certain date range and then add the number of cells together that those dates fall within a certain amount of time from the current date. Off the top of my head, use COUNT. |
|
Quoted:
To identify your dates that are older then today add a new column. =if("your date cell" <now(),1,0) and copy that next to each date. Now you have a 1 next to every date before today. Sum that column and you'll have the count. A reference to another sheet looks like =sheet2!A1. I prefer to just start typing my formula and then going to the other sheet and clicking on the cells I want. Is there a way to write the formula just for it to look for <=TODAY()-365 or something, and then figure out how many cells there are that fall under that parameter? Basically I have 100 dates. On a different sheet I want it to tell me how many of those dates are 11 months old or sooner, between 11 months old and 12, and then over 12 months old. |
|
Quoted:
=COUNTIF([range],"<="&TODAY()-365) [range] = cells that contain the dates There are the dates I have entered and the formula, it's coming back with 0 regardless of what I change the dates to. 11/5/2011 11/5/2008 7/27/2010 11/5/2011 11/5/2009 11/5/2002 =COUNTIF(A11:A16, "<=&TODAY()-365" ) |
|
Quoted:
Quoted:
=COUNTIF([range],"<="&TODAY()-365) [range] = cells that contain the dates There are the dates I have entered and the formula, it's coming back with 0 regardless of what I change the dates to. 11/5/2011 11/5/2008 7/27/2010 11/5/2011 11/5/2009 11/5/2002 =COUNTIF(A11:A16, "<=&TODAY()-365" ) Change the part in red to "<=" and delete the part in blue. |
|
Quoted:
Quoted:
=COUNTIF([range],"<="&TODAY()-365) [range] = cells that contain the dates There are the dates I have entered and the formula, it's coming back with 0 regardless of what I change the dates to. 11/5/2011 11/5/2008 7/27/2010 11/5/2011 11/5/2009 11/5/2002 =COUNTIF(A11:A16, "<=&TODAY()-365" ) The formula should be this =COUNTIF(A11:A16, "<="&TODAY()-365 ) What is happening her is you are concatenating 2 statements into one <= and TODAY()-365. Excel reads it as <=TODAY()-365. I don't know why that needs to be done but it does and it works. |
|
Quoted:
Quoted:
=COUNTIF([range],"<="&TODAY()-365) [range] = cells that contain the dates There are the dates I have entered and the formula, it's coming back with 0 regardless of what I change the dates to. 11/5/2011 11/5/2008 7/27/2010 11/5/2011 11/5/2009 11/5/2002 =COUNTIF(A11:A16, "<=&TODAY()-365" ) This may be a bit trivial, but do you have your dates actually formatted as dates? J |
|
Quoted:
Quoted:
Quoted:
=COUNTIF([range],"<="&TODAY()-365) [range] = cells that contain the dates There are the dates I have entered and the formula, it's coming back with 0 regardless of what I change the dates to. 11/5/2011 11/5/2008 7/27/2010 11/5/2011 11/5/2009 11/5/2002 =COUNTIF(A11:A16, "<=&TODAY()-365" ) Change the part in red to "<=" and delete the part in blue. Sweet, that works! How would I get it to look for a date older than 335 days but newer than 365? |
|
Quoted:
Quoted:
Quoted:
=COUNTIF([range],"<="&TODAY()-365) [range] = cells that contain the dates There are the dates I have entered and the formula, it's coming back with 0 regardless of what I change the dates to. 11/5/2011 11/5/2008 7/27/2010 11/5/2011 11/5/2009 11/5/2002 =COUNTIF(A11:A16, "<=&TODAY()-365" ) This may be a bit trivial, but do you have your dates actually formatted as dates? J Yeah, it worked once I corrected the quotation. |