Thread: When The Mth Changes (Excel 2000)

1. When The Mth Changes (Excel 2000)

Good Morning,

I have the following code that COUNTS the difference between dates. In other words....if on 6/12, 7 records (rows) were inserted, then on 6/13, 2 records were inserted the cell where the formula for SumForDate is would be 7 then 2. It works great, I would like to know if there is a way to modify it a just a bit so it can recognize the change in the month. i.e. I need it to COUNT all the records that were inserted during the month of June. Once July's date begins...show the total count for June...then at the end of July and August begins, provide the COUNT for July, etc. (the count should start over at 0).

Is this even possible. I think so since I got the below code to works fine....just not sure how to tell it to recognize a new month.

Thanks.

Function CountForDate(rng As Range) As Long
Application.Volatile
End Function

2. Re: When The Mth Changes (Excel 2000)

The function you posted will only work if the workbook has a special structure you haven't told us about. Can you tell us something about the setup of the worksheet?

3. Re: When The Mth Changes (Excel 2000)

Do you have the dates stored in cells?
If so you could use the array (confirm with ctrl-shift-enter) formula
=SUM(IF((YEAR(\$A\$1:\$A\$2500)=2004)*(MONTH(\$A\$1:\$A\$2 500)=6),1))

To count the number of cells in the range A1:A2500 (change as desired) that are June 2004 dates.

For July:
=SUM(IF((YEAR(\$A\$1:\$A\$2500)=2004)*(MONTH(\$A\$1:\$A\$2 500)=7),1))

etc

Steve

4. Re: When The Mth Changes (Excel 2000)

Hans,

The only thing is a formula: =IF(P22<>"",CountForDate(P22),""). I put this formula in column "Q". Once the date is entered in a cell in Column "P", then the total is provided in "Q". That's the only special thing.

Steve,

Wouldn't your option have to be entered each time or rather the F9? If so, I'm trying to avoid this, I would rather that once the date is entered the COUNT is provided.

5. Re: When The Mth Changes (Excel 2000)

I think that this modification to Steve's array formula does what you want:

<pre>=SUM(IF((YEAR(\$A\$1:\$A\$2500)=2004)*(MONTH(\$A\$1 :\$A\$2500)=MONTH(NOW())-1),1))
</pre>

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•