Please help!! Vacation Accrual Formula

[cplusHR]

OK, I want to created this worksheet with the Vacation Accural Formula from scratch.

Basically, I need to create a spreadsheet showing number of accumulated
vacation days for an employee based on the number of days they accrue
per day per year.

We created a formula that basically used the current date (of the day
you actually accessed the spreadsheet) to calculate the number of days
(with like 2 decimal places) the employee had accrued to date.

This is the info we used to generate the number:

Anniversary date
Days accrued for year 1, 2, 3, 4, 5...
Less than A year does not have any days.
Year one - 15 days
Year two - 15 days
Year three - 15 days
Year four - 17 days
Year five - 17 days
Year six - 17 days
Year seven - 19 days
Year eight - 19 days
Year nine - 19 days
Year ten - 19 days
Year Eleven - 21 days
Further more is going to be 21 Days.

Also taking in consideration that any days not used the previous year,
will roll over to the next year. The limit is only within 2 years, after that the days will be over.

Lets Say, in Main Sheet I will have as Follows;

A2 Employee Number
B2 Employee
C2 Start Date
D2 Today ()
E2 Accumulated Vacation Days
F2 Remaining Vacation Days
G2Vacation Available
H2 Unused Vacation from last year

The Second sheet, is to calculate the vacation days that are being taken.
Or having individual sheet for each employee since there are only 25 staff max.

If you need any further clarification toward this matter, I would be glad to let you know to assist with this nightmare that I am having.

Any Help would be apprciated,can someone please help me with this?

Thank you.

 

[cplusHR]

This may be over simplistic, but have a look.

http://dl.dropbox.com/u/9568360/Vacations 2011.xlsx

AMAS

Thank you, I had a look and actually it looks good. I like to note the column from two years wont be necessary as the count should be last year and this year.

I think I need an extra column for Used Vacation Days, since the other sheets are going to give the vacation taken.

Please Amas, how can I fix the correct formula for B2 and C2,

=LOOKUP("Record",'John Doe'!L2)

this is for B2

='John Doe'!AD2

for C2

Once I have the right formula it should be easy to drag, I think, I might be wrong.

Thanks.

 

[AMAS]

Sorry I don't follow. What do you want in B2 and C2? What is the formula supposed to retrieve?

AMAS

 

[cplusHR]

Hi Amas

I want to retrieve in B2 the Name of employee on other sheets and in C2
the date of enrollment of same Employee in the exact same sheet.

Of course I will need to go on for B3 and C3, for another employee in different sheet. As you noticed that other sheet are according to the names of employees. Once the formula is there, I will be able do so for the others and even once I create a new one.

 

[cplusHR]

I am sorry to trouble you with this as well.

I noticed something about formula in D2,

=DAY(C2)&"/"&MONTH(C2)&"/"&YEAR(TODAY())

Is this better or the below;

=DAY(Today())&"/"&MONTH(Today())&"/"&YEAR(TODAY())

I was wondering if a simple the formula of Today() would do?

Thanks.

 

[AMAS]

I personally liked Today(), but I see your reasoning. In the end Today() should give you accurate results, but you will have to manually know when one year is exactly up. I am thinking a simple vba solution to this may be helpful to automatically move this year's unused vacation days from to last year's vacation days slot once one year + one day have passed. Let me try to play with this over the weekend, but in the meantime Today() should be fine. I also didn't get a chance to test your new formula yet, but will try over the weekend.

AMAS

 

[cplusHR]

Ok Sure thing and Thank you Amas. I will wait your reply.

 

[cplusHR]

Any help please...

 

[cplusHR]

I personally liked Today(), but I see your reasoning. In the end Today() should give you accurate results, but you will have to manually know when one year is exactly up. I am thinking a simple vba solution to this may be helpful to automatically move this year's unused vacation days from to last year's vacation days slot once one year + one day have passed. Let me try to play with this over the weekend, but in the meantime Today() should be fine. I also didn't get a chance to test your new formula yet, but will try over the weekend.

AMAS

Any luck Amas?.. If it difficult, then I will adjust the file into more easier equation. So we can avoid VB formula.

http://www.box.net/shared/a2t7kzajao9xxcvu8rsz

I have attached different file to minize the issues that I am going through. I summed each employee to one sheet instead of each person on separate Sheet.

Thank you.

 

[AMAS]

Hi,

Sorry I have been off the grid with personal issues. I am not sure if I will have time to work on this right now, but will do my best. Hopefully someone else can step up and finish the remaining parts. Once I clear my head, I will have a look back and see if there are still any outstanding issues.

AMAS

 

[cplusHR]

that will be much appreciated.