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

Asked By Linda43 on 09-Jun-08 04:31 PM
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




HelpFrom replied on 09-Jun-08 04:54 PM
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.
Linda43 replied on 10-Jun-08 03:55 PM
The formula worked great, but, how do I change it to take into account leap
years?  Thanks again.