[Linda432]

I am using Excel 2000.

I have a spreadsheet that calculates the hours used in the last rolling 12
months for FMLA. The fomula I have works but I need to allow for leap years.
How do I change the formula?

The formula in column C is: =sumif($A$2:A2,""&A2-365,B$2:B2)

Col A Col B Col C
Date Hours used Hours in past 12 month period
1-1-07 1.00 1.00
2-15-07 5.00 6.00
2-5-08 3.00 8.00

Thanks for your help.
Linda

[Harald Staff]

try
=sumif($A$2:A2,""&DATE(YEAR(A2)-1,MONTH(A2),DAY(A2)),B$2:B2)

HTH. Best wishes Harald

"Linda432" skrev i melding
...
I am using Excel 2000.

I have a spreadsheet that calculates the hours used in the last rolling 12
months for FMLA. The fomula I have works but I need to allow for leap
years.
How do I change the formula?

The formula in column C is: =sumif($A$2:A2,""&A2-365,B$2:B2)

Col A Col B Col C
Date Hours used Hours in past 12 month period
1-1-07 1.00 1.00
2-15-07 5.00 6.00
2-5-08 3.00 8.00

Thanks for your help.
Linda

[Andrea Jones]

You could use

=SUMIF($A$2:A2,""&DATEVALUE(MONTH(A2)&"/"&DAY(A2)&"/"&YEAR(A2)-1),B$2:B2)

Andrea Jones
www.stratatraining.co.uk
www.wrekinpublishing.com

"Linda432" wrote:

I am using Excel 2000.

I have a spreadsheet that calculates the hours used in the last rolling 12
months for FMLA. The fomula I have works but I need to allow for leap years.
How do I change the formula?

The formula in column C is: =sumif($A$2:A2,""&A2-365,B$2:B2)

Col A Col B Col C
Date Hours used Hours in past 12 month period
1-1-07 1.00 1.00
2-15-07 5.00 6.00
2-5-08 3.00 8.00

Thanks for your help.
Linda

[Linda432]

Thank you both. The formulas worked like a charm!!

Linda

"Andrea Jones" wrote:

You could use

=SUMIF($A$2:A2,""&DATEVALUE(MONTH(A2)&"/"&DAY(A2)&"/"&YEAR(A2)-1),B$2:B2)

Andrea Jones
www.stratatraining.co.uk
www.wrekinpublishing.com

"Linda432" wrote:

I am using Excel 2000.

I have a spreadsheet that calculates the hours used in the last rolling 12
months for FMLA. The fomula I have works but I need to allow for leap years.
How do I change the formula?

The formula in column C is: =sumif($A$2:A2,""&A2-365,B$2:B2)

Col A Col B Col C
Date Hours used Hours in past 12 month period
1-1-07 1.00 1.00
2-15-07 5.00 6.00
2-5-08 3.00 8.00

Thanks for your help.
Linda