Need Formula to Calculate Hours Accrued During Year

[Hasgin]

Hi,
I have no idea how to do this ... hopefully it's possible.

I'm trying to determine how much vacation a person accrues during the fiscal year (10/1/03 - 9/30/04).

All staff accrue 8.667 hours/mo when they are on staff 1-48 months. They then begin to accrue 10 hours/mo when they've been on staff 49-108 months. 109 months on, they accrue 13.334 hours/mo.

I originally had used this formula and thought I had it: =IF(G4<12,G4*6.667,IF(G4<48,12*6.667,IF(G4<108,12*10,IF(G4>=108,12*13.334)))). (G4 refers to the cell which contains the # of months a person has been on staff.)

However, what this formula doesn't account for is if someone switches to a new accrual rate mid-year. I am drawing a complete blank on how to set this up formula-wise.

I would greatly appreciate any suggestions anyone can give!
ray:

 

[Joe Was]

This link is about the same process:

Your formula should start with the largest month rate first then work down to the lowest.

http://www.mrexcel.com/board2/viewtopic.php?t=100426&highlight=

 

[Hasgin]

Thanks so much! That was helpful and has helped me to get my thinking process going. However, I'm having a hard time figuring out how to get it to just operate on a 12 month period.

For instance, say by 9/30/04, a person has been here 58 months. That means that he/she accrued vacation at the following rate:

1) 47 mo = 8.667 hours
2) 48 mo = 8.667 hours
3) 49 mo = 10 hours
4) 50 mo = 10 hours
5) 51 mo = 10 hours
6) 52 mo = 10 hours
7) 53 mo = 10 hours
8) 54 mo = 10 hours
9) 55 mo = 10 hours
10) 56 mo = 10 hours
11) 57 mo = 10 hours
12) 58 mo = 10 hours
Total = 117.33 hours

That formula may address that and I'm just not seeing it. If not, though, is there a way to express that?
Thanks again for your help!

 

[Ian Mac]

However, what this formula doesn't account for is if someone switches to a new accrual rate mid-year. I am drawing a complete blank on how to set this up formula-wise.

There appear to be a few questions need answering.

Can you give a bit more about the rules required for this, please.

Why is it 8.666 for 12 months? This works out at 104hrs per year.
What is your working week? no hrs/days.
What if someone works part time?
What happens when someone starts in Nov 03? Does the next fiscal (9/30/04) count as 1 year service? or do they wait until Nov 04?
Are we saying that in the above example PersonA accrues 8.666 for the time they work in 03/04 (i.e. pro-rata) then for 1 month in 04/05 8.666 then 11 months at 10?
If the above is assumed are the figures rounded up/down to the begin/end of the month? Or is it calulated on actual days, say PersonA starts Nov 15th does he get 46 days in 04/05 at 8.666?

How do you have this all recorded?

Edit: I've looked at the other reply, but I think the bare bones of the calculation need to be looked at first and answering the questions above will provide some solid grounding.

BTW, sounds interesting!
Cheers,

 

[Hasgin]

Thanks so much for getting back to me!

* Good call on the 8.667 hours question. I KNEW I shouldn't have tackled this before leaving work for the day! My brain wasn't quite functioning correctly. A person actually accrues 6.667 hours/mo for his/her first 48 months of service...working out to 80 months (2 weeks) a year.
* This is based on a 40 hour workweek...8 hours a day, 5 days a week.
* A person who works part-time doesn't accrue vacation hours. It's just for full-time staff.
* A year of service counts as 1 calender year (i.e. 11/1/03 - 11/1/04)
* I'm afraid that I didn't exactly understand your question regarding PersonA. In my example, PersonA has been with us 58 months by 9/30/04. Here's his/her vacation accrual breakdown for the past 12 months. Vacation hours accrue automatically at the end of each month. I hope this is what you were wanting.

1) 10/31/03 (has been with us 47 mo by this point) = 6.667 hours
2) 11/30/03 (48 mo) = 6.667 hours
3) 12/31/03 (49 mo) = 10 hours
4) 1/31/04 (50) = 10 hours
5) 2/28/04 (51) = 10 hours
6) 3/31/04 (52) = 10 hours
7) 4/30/04 (53) = 10 hours
8) 5/31/04 (54) = 10 hours
9) 6/30/04 (55) = 10 hours
10) 7/31/04 (56) = 10 hours
11) 8/31/04 (57) = 10 hours
12) 9/30/04 (58) = 10 hours
Total = 113.334 hours

*Vacation hours are calculated at the end of the month. Say someone starts 9/10/04. At the end of the month (9/30/04), he/she will have accrued 6.667 hours. At the end of 10/31/04, he/she will 13.33 hours total accrued.
*The accrual transactions are recorded in in our staff database (Lawson). It actually figures it automatically for us. However, we are adding a group of folks from our field offices to our staff d-base. We'd like this Excel sheet to automatically calculate how many vacation hours a person has accrued this fiscal year (10/1/03 - 9/30/04) just by entering in his/her start date.

I hope this answers your questions. Thanks for your help!