Calculate events between 2 dates

[nc22]

Hello,

I would need to calculate instances that happen in a span of a week (Sunday - Sarurday).

I am using a table named Table2 and a Column called Due Date.

From there several events are entered with the due date in the due date column. I would need to find out how many events are going to happend in the current week while using a formula.

Please Help

 

[schielrn]

Welcome to the Board!

Any of these should work depending on your version:

Excel Workbook
	C	D	E	F	G	H	I
1	Due Date			Date1	Date2	# of Events
2	1/8/2011			1/8/2011	1/12/2011	3	Any version
3	1/9/2011			1/8/2011	1/12/2011	3	Any version
4	1/17/2011			1/8/2011	1/12/2011	3	Excel 2007 or newer
5	1/12/2011
6	1/7/2011
7	1/25/2011
Sheet1

Cell Formulas
Range	Formula
H2	=COUNTIF(C2:C7,"< ="&G2)-COUNTIF(C2:C7,"< "&F2)
H3	=SUMPRODUCT((C2:C7 > =F3)*(C2:C7< =G3))
H4	=COUNTIFS(C2:C7," > ="&F4,C2:C7,"< ="&G4)

Hope that helps.

 

[nc22]

Hi and Thank you for your welcome message.

I have not tried the formula yet but what would it be if instead of cell numbers I would want to use "Table2 as the table and the Column name Due Date" since the entries will be changed daily as soon as the job is done and could get very large. Also I would not want the date range to be posted but have excel use its weekday function and figure out the weekday dates from sunday to saturday.

Hope this is clear as mud!

 

[schielrn]

You can use whole column ranges for countif and in excel 2007 you can use whole column ranges for countifs or sumproduct.

I'm not exactly sure what you mean if you just put in sunday and saturday and no date range, how it knows what date range to count through?

Can you post any sample data and expected results?

You can copy and paste directly from Excel if you want.