dates

[pat brown]

Hi, I am sure this is simple but cannot for the life of me work it out....

I have a date in a cell of 4-Apr-2008. In another part of the model I have a column headed Apr-2008 (which is really 1/4/08). Under that Apr-08 column I want it to look at the column which has the detailed date in it and count how many of them are in the month of April.

I have been using =sumif but the problem is that whilst the detail can be any date in the month, the one I am using as the heading is always 1/4/08, so will not match anything other than 1st of the month.

TIA
Pat

[Stuie]

Hi pat brown,

Are you trying to count how many days are in the month or in the month up until a certain date?

[daddylonglegs]

If 1-Apr-08 is in A1 and you want to count dates that match that month from B1:B100 try

=SUMPRODUCT(--(B1:B100-DAY(B1:B100)+1=A1))

or

=SUMPRODUCT(--(TEXT(B1:B100,"mmyy")=TEXT(A1,"mmyy"))

[pat brown]

Hi

I have a list of various dates - they are contract end dates. For example

4/4/08
5/6/08
6/4/08
31/12/08

In another part of the model I have a column for each month and under each month I need to have the NUMBER of contracts finishing it that month, so based on the above....

Apr-08 2 finishers
Jun-08 1 finisher
etc..

The cell headings on the part I want the analysis in says Apr-08 but underneath it, it defaults to 1/4/08. When using SUMIF it cannot match the dates, so I really need to have a working cell that just shows the dates as 1st of each month so the SUMIF will work....unless of course there is another way.

Hope this makes sense

Pat

[pat brown]

That's excellent, thank you

The =SUMPRODUCT sorted it out.

Pat