leap year date issue in sumif formula

[Mr._Bill]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Power PC

=SUMIFS($D$10:$D$9237,$B$10:$B$9237,">=02/01",$B$10:$B$9237,"<=02/29",$C$10:$C$9237,"=gn")

The above formula works great, but only in leap years.

This formula sits in cells c2:c6 under the heading of Feb. Jan starts in B2 and the respective months follow in the other columns d thru M.

The data entry is below these cells starting at a10 including columns through e10. When you enter a date anytime past 2/29 this formula returns results in Jan, Feb and the month of the actual date entered.

The problem only exists in years that are not leap years. Now the easy fix is to just change the 2/29 to 2/28 but I create spreadsheets for clients that utilize date driven sumif formulas and to manage these every leap year really isn't convenient . Any ideas on this or is this an app issue that needs a programming fix?

 

[Bob Greenblatt]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Power PC

=SUMIFS($D$10:$D$9237,$B$10:$B$9237,">=02/01",$B$10:$B$9237,"<=02/29",$C$10:$C
$9237,"=gn")

The above formula works great, but only in leap years.

This formula sits in cells c2:c6 under the heading of Feb. Jan starts in B2
and the respective months follow in the other columns d thru M.

The data entry is below these cells starting at a10 including columns through
e10. When you enter a date anytime past 2/29 this formula returns results in
Jan, Feb and the month of the actual date entered.

The problem only exists in years that are not leap years. Now the easy fix is
to just change the 2/29 to 2/28 but I create spreadsheets for clients that
utilize date driven sumif formulas and to manage these every leap year really
isn't convenient . Any ideas on this or is this an app issue that needs a
programming fix?

Instead of "<=2/29" why not use <=Date(year(now(),3,1)-1

 

[Bob Greenblatt]

Instead of "<=2/29" why not use <=Date(year(now(),3,1)-1

Sorry about any confusion, the above formula should be:
<=Date(year(now()),3,1)-1