I have a workbook with 115 worksheets. I am retrofitting a new date
calculation in each worksheet via a macro.

The purpose of this is to add one month in each row BUT not allow the day to
be greater than the last day of the month. So if the issue date is December
31, a renewal date cannot be February 31, it can be either February 28th or
29th if in a leap year.

This formula starts in Column D in row 20 and, in the example shown below,
is row 31 - the row in which the date should be 1/29/05, not 04 - the start
of a new year.

The Day portion of the below formula takes advantage of the Date function in
which if you enter a 0 for the Day, you get the last day of the previous
month.

=DATE(YEAR(D31+IF(C31=12,1,0)),IF(C31=12,MONTH($D$ 20),MONTH(D31)+1),MIN(DAY(DATE(YEAR(D31)+IF(MONTH( D31)=12,1,0),IF(MONTH(D31)+1=13,1,MONTH(D31)+1)+1, 0)),DAY($D$20)))

The absolute reference of $D$20 is the issue date of an insurance policy.
Column "C" in the above formula counts the month in the the policy year.

Mo Date

1 01/29/04
2 02/29/04
3 03/29/04
4 04/29/04
5 05/29/04
6 06/29/04
7 07/29/04
8 08/29/04
9 09/29/04
10 10/29/04
11 11/29/04
12 12/29/04
1 01/29/04
2 02/29/04


Date in Month 1 is hardcoded.

The basic question is why does the formula work for all but 8 cases out of
the 115 in this workbook? Am I just lucky with the ones on which it does
work?