Working with a range of dates.

[Alasbabylon]

How can I check a column of dates and count all the times a date falls in a specific range?
For example, I need to check a sheet for all instances where Doe, Jane has a project due during the month of January. Ditto for February, March, and April.
I also will have to perform the same calculation for each person in the group.
We also need to know how many time each person has a project due during the current week. I'm stumped.



Oh, icing on the cake, Is there a way to set up a cell so that I can pop up a calendar, select a date, and have that date appear in that cell?

[Jacob Hilderbrand]

Can you post an attachment of how the data is layed out.

For the calendar try this example.

[Alasbabylon]

Sure. I tried the calendar, but it tells me I could not load an object because it is not availble on this machine.

In my file, I need to count on page 1 for each person, the number of projects they have due in each month AND in the current week. We are using Column D on the POD page to calculate the due date.

[Jacob Hilderbrand]

Ok, we can use SUMPRODUCT to make these calculations.

First we need to add some dates to search by. I inserted a row (Row 2) in Sheet1. The I put in these formulas.

S2 = 1/1/2005
 T2 = 2/1/2005
 U2 = 3/1/2005
 V2 = 4/1/2005
 W2 = 5/1/2005
T3 =SUMPRODUCT(--('POD Adhoc Report'!$B$3:$B$1000=Sheet1!$A3),--('POD Adhoc Report'!$D$3:$D$1000>=Sheet1!S$2),--('POD Adhoc Report'!$D$3:$D$1000<T$2))

Fill across and down.

You can do this within a two week period as well. Just make the dates what you want to search between.

See attachment.

[Jacob Hilderbrand]

For the calendar try this.

In the VBE
Insert | User Form
Right Click on the Control Toolbox and select Additional Controls...
Browse for Calendar Control #.# (The # may vary depending on your version)
Add that control
Add the control to the User Form
Then refer to the code in the link I provided and add it to the User Form

[Jacob Hilderbrand]

To get the date for the current week (starting on Sunday) you can use this formula.

A1=TODAY()-WEEKDAY(TODAY())+1

Then to get the following Saturday just use this formula.

A2 = A1 + 6

[Alasbabylon]

THANK YOU ALL! This is an excellent solution, and I apologize for being so late with my thanks, but I've been out of the office lately. You all have provided me a huge help, and I am VERY grateful for this.

Alas Babylon