How do I calculate FMLA hours used in the last rolling 12 months
 

I am using Excel 2000.

I have to create a spreadsheet that calculates the hours used in the last
rolling 12 months.

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 8.00 8.00

What formula will I enter to get the amounts in Col. C? Will I have to
enter a different formula for each row in Col. C? Thanks.

Linda

How do I calculate FMLA hours used in the last rolling 12 months
 

Assuming that your first date (1-1-07) is in row 2, then in C2 put this formula
=SUMIF($A$2:A2,"" & A2-365,B$2:B2)
as you fill that formula on down the sheet, it will automatically do the
rolling-total for all dates that are within the past 365 days (actually may
be 364, but testing can confirm it for you - if 365, then change 365 in the
formula to 366). You won't have to change the formula, it fills down very
nicely.


"Linda432" wrote:

I am using Excel 2000.

I have to create a spreadsheet that calculates the hours used in the last
rolling 12 months.

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 8.00 8.00

What formula will I enter to get the amounts in Col. C? Will I have to
enter a different formula for each row in Col. C? Thanks.

Linda

How do I calculate FMLA hours used in the last rolling 12 mont
 

The formula worked great, but, how do I change it to take into account leap
years? Thanks again.

"JLatham" wrote:

Assuming that your first date (1-1-07) is in row 2, then in C2 put this formula
=SUMIF($A$2:A2,"" & A2-365,B$2:B2)
as you fill that formula on down the sheet, it will automatically do the
rolling-total for all dates that are within the past 365 days (actually may
be 364, but testing can confirm it for you - if 365, then change 365 in the
formula to 366). You won't have to change the formula, it fills down very
nicely.


"Linda432" wrote:

I am using Excel 2000.

I have to create a spreadsheet that calculates the hours used in the last
rolling 12 months.

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 8.00 8.00

What formula will I enter to get the amounts in Col. C? Will I have to
enter a different formula for each row in Col. C? Thanks.

Linda