Count dates between two dates

[rganz]

Hello All,

Here is what I am looking to do. Below is the data and sample output. I am currently doing this by hand for hundreds of records each week. I want to automate the process.

I need to create a formula that looks at the “Date of Incident” from the Data and checks if fails in between the dates in the Output chart, then check if the Functional Group = CSTP-EPG, if yes then add 1 to the total for that week for CSTP-EPG.
I need to do this for each Functional Group.

Data:

Date of Incident Functional Group
11-Sep-09 CSTP-LD
15-Oct-09 CSTP-EPG
2-Nov-09 CSTP
2-Nov-09 CSTP-LD
9-Oct-09 CSTP
16-Sep-09 CSTP-FX
14-Sep-09 CSTP
30-Oct-09 CSTP-DEV
29-Oct-09 CSTP-EPG




Output:

Week the Incident Occurred CSTP-LD CSTP-EPG CSTP-FX
11/16/2009 - 11/22/2009 1
11/9/2009 - 11/15/2009 1
11/2/2009 - 11/8/2009 4 2
10/26/2009 -11/1/2009 1 1
10/19/2009 -10/25/2009
10/12/2009 -10/18/2009 1
10/5/2009 -10/11/2009 1 1
9/28/2009 -10/4/2009 1
9/21/2009 - 9/27/2009
9/14/2009 - 9/20/2009 1 2
9/7/2009 - 9/13/2009 1
8/31/2009 -9/6/2009 1 1
8/24/2009 - 8/30/2009 1 1

Thanks for your help!!!!

Bob

[rganz]

Sorry the for formating changes after the post. Hope you can figure out my Data and Output info.

[-R3-]

I'm nowhere near an expert, however I just did something similar to this using the COUNTIF/COUNTA formula, in the Validation menu.

If this helps in any way, i'm glad, otherwise, think of my post as more of a bump.

[rganz]

Thanks I tried them as well but I still dont know how to test bewtween the dates and for CSTP.

I am sure it is easy. I just cant seem to figure it out. lol

[khamilton]

Don't know if this is what you are looking for but I'll give it a shot
;Test if date is between two date then count.xlsx

[ConneXionLost]

Hi rganz,

If possible, arrange the start and finish dates of your output sheet so they are in seperate columns and recognized as dates instead of text.

Enter this formula in cell C2 of your output sheet:

Please Login or Register  to view this content.

Adjust ranges as required, then autofill across & down.

Cheers,

[rganz]

KHamlton and ConneXionLost you guys rock! Perfect!!! Thank you so very much!

[khamilton]

your welcome