SUMPRODUCT leap year error

**plsm5882** · 01-30-2013, 02:42 PM

Hi.

Could someone help please, as I can't make head or tales of this...

SUMPRODUCT((Income!$P$3:$P$500)*(Income!$S$3:$S$500=$B24)*(Income!$J$3:$J$500>=DATEVALUE("01/2/2013"))*(Income!$J$3:$J$500<=DATEVALUE("29/2/2013"))) = #VALUE!

SUMPRODUCT((Income!$P$3:$P$500)*(Income!$S$3:$S$500=$B24)*(Income!$J$3:$J$500>=DATEVALUE("01/2/2013"))*(Income!$J$3:$J$500<=DATEVALUE("28/2/2013"))) = WORKS

I don't understand why that doesn't work...

Thanks

Rgds
Peter

**martindwilson** · 01-30-2013, 02:43 PM

2013 is not a leap year therfore datevalue("29/2/2013") will result in an error

**Norie** · 01-30-2013, 02:44 PM

Peter

29/2/2013 isn't a valid date.

**plsm5882** · 01-30-2013, 02:45 PM

DOH! My leap year calc must be wrong: IF(IF(OR(MOD(D2,400)=0,AND(MOD(D2,4)=0,MOD(D2,100)<>0)),1, 0)=0,28,IF(IF(OR(MOD(D2,400)=0,AND(MOD(D2,4)=0,MOD(D2,100)<>0)),1, 0)=1,29))

**Norie** · 01-30-2013, 02:50 PM

What values in D2?

If it's today's date the leap year formula returns 29, if it's 2013 it returns 28.

**martindwilson** · 01-30-2013, 02:50 PM

=IF(OR(MOD(d2,400)=0,AND(MOD(d2,4)=0,MOD(d2,100)<>0)),29, 28)

**plsm5882** · 01-30-2013, 02:56 PM

It was a little fiscal year error on my part. You have all helped a lot thanks!

Updated formular (Thanks Martin): =IF(OR(MOD((D2+1),400)=0,AND(MOD((D2+1),4)=0,MOD((D2+1),100)<>0)),29, 28)