Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Challenge: Return the rightmost 12 values (rolling 12 months) | Excel Worksheet Functions | |||
I need help with a formula for rolling twelve months | Excel Discussion (Misc queries) | |||
ROLLING 12 MONTHS PLEASE HELP ME!!!!! | Excel Discussion (Misc queries) | |||
rolling 12 months | Excel Worksheet Functions | |||
rolling months | Excel Discussion (Misc queries) |