Quote:
|
Originally Posted by Ron Coderre
For the between scenario, this formula is a bit shorter:
B1: =SUMPRODUCT((A1:A100(--"01/01/2003"))*(A1:A100(--("01/01/2004"))))
Easiest, though, is to enter the min and max dates in separate cells and
reference them...
B1: (min date to include)
B2: (max date to include)
Count of dates between those 2 dates, inclusive:
B3: =SUMPRODUCT((A1:A100=B1)*(A1:A100=B2))
Does that help?
***********
Regards,
Ron
XL2002, WinXP
"Ron Coderre" wrote:
For a list of dates in A1:A100
You have a couple options:
These formulas EXCLUDE 01/01/2003....
B1:
=SUMPRODUCT((A1:A100DATEVALUE("01/01/2003"))*(A1:A100DATEVALUE("01/01/2004")))
or
B1: =COUNTIF(A1:A100,"01/01/2004")-COUNTIF(A1:A100,"=01/01/2003")
If you want the count of any dates within 2003....
B1: =SUMPRODUCT(--(YEAR(A1:A100)=2003))
Does that help?
***********
Regards,
Ron
XL2002, WinXP
"SR89" wrote:
Hi,
I can't figure out how to count how many occurances fall between two
dates. For example, how many of the dates in a column are Jan 01,
2003 and Jan 01, 2004.
COUNTIF will count one occurance only, so I think that I have to use
maybe SUMPRODUCT but am now sure how the formula would work.
Any ideas?
Thanks,
--
SR89
|
Hmm, I must be fairly imcompetent as I can't seem to get these to work so I'll lay out the problem in it's entirety since I probably did not give enough info the first time.
I have to find the number of occurances between two dates as mentioned. This database will be getting updated on a continual basis as more data is entered. I have broken down each year into quarterly sections. They are follows (using this year as the example) and they are all inclusive dates:
Apr 1, 06 - June 30, 06
Jul 1, 06 - Sept 30, 06
Oct 1, 06 - Dec 31, 07
Jan 1 ,07 - Mar 31, 07
So now I need to figure out the dates in the database that are between each of those quarterly sectors (there is also data in the database from previous years).
I thought it would be easiest to enter a formula using the COUNTIF and use the crtieria of all dates that were greater then Apr 1, 06 and less then July 1, 06. But that doesn't work as COUNTIF can only have one criteria. As well, I cannot use any sort of COUNTIF "-" COUNTIF since any dates that are entered after June 30 1, 06 will be picked up by the one side of the COUNTIF equation (the greater then April 1, 06) but not the other (the less then June 30, 06).
Sorry for making this so long, hope that clarifies it a bit!!! As well, when a formula has "--", what does that mean? Do you actually need to enter that as part of the formula or what do you enter in it's place? Sorry if that is dumb q, just unsure of what it means.
Thanks so much!!!
Steve