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]

Please any assistant or some formula that can get me started would be appreciated.

 

[AMAS]

Hi,

This will get you (hopefully) on the right track. It will give you the number of days of accumulated vacation days for this year:

Put this in E2 and drag down.

=LOOKUP((D2-C2)/365,{0,1,4,7,11},{0,15,17,19,21})

Now I am a bit confused about how to calculate the rest. Shouldn't there be a cell for the number of vacation days used this year so that you can caluclate F2?

AMAS

 

[MikeWx]

Do you also have a date when accumulated vacation for this year is placed in Vacation Available?

Is that also the same date that any remaining Vacation Available is moved to Unused Vacation from last year, replacing the prior value?

When someone takes vacation, is Unused Vacation from last year reduced to zero first before the Vacation Available balance is decremented?

 

[cplusHR]

Hi,

This will get you (hopefully) on the right track. It will give you the number of days of accumulated vacation days for this year:

Put this in E2 and drag down.

=LOOKUP((D2-C2)/365,{0,1,4,7,11},{0,15,17,19,21})

Now I am a bit confused about how to calculate the rest. Shouldn't there be a cell for the number of vacation days used this year so that you can caluclate F2?

AMAS

I will add the number of vacation days used this year, but I thought it would be the same one which is under Vacation used, right? If it helps I can add it.

 

[cplusHR]

Do you also have a date when accumulated vacation for this year is placed in Vacation Available?

Is that also the same date that any remaining Vacation Available is moved to Unused Vacation from last year, replacing the prior value?

When someone takes vacation, is Unused Vacation from last year reduced to zero first before the Vacation Available balance is decremented?

Yes I have a column which is Vacation Available. So what I am trying to do is that I create a new sheet that I should link it to this cell. In the new sheet I input the dates taken which should sum the total of days, then it display in the Main sheet.

The unused vacation from last year, should add to the Vacation available. But only two years limit, as after this those days are gone.

Lets say, someone have 15 days from last year, so he used 10 days only, the Vacation Available balance should display 5 days plus any days from this year. So the Unused Vacation from last year will roll over to the Vacation Available within a 2 year limit.

I hope I was clear.

 

[cplusHR]

I have Added I2 Used Vacation Days.

Which will be for the Dates Used.

I another thing that I am trying to do is;

in B2 Employee name is in Sheet called Employee Name
ex: John Doe, sheet called John Doe and it is available in L2
I want to use the vlookup or lookup but it is not working.

same things for C2 Starting Date
and is located in AD2 at the same sheet "John Doe"

I hope it is clear as I am not sure, my explanation is a bit comfusing.

I attached the execl to give better idea, I hope it helps.


-- removed inline image ---

 

[AMAS]

Hi,

Could you upload an example workbook?

Here are some free posting websites...

Box: http://www.box.net/files
MediaFire: http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
FileDropper: http://www.filedropper.com
RapidShare: http://www.rapidshare.com

AMAS<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

 

[cplusHR]

Hi,

Could you upload an example workbook?

Here are some free posting websites...

Box: http://www.box.net/files
MediaFire: http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
FileDropper: http://www.filedropper.com
RapidShare: http://www.rapidshare.com

AMAS<!--?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /--><o></o>

I have uploaded the file in this link please.

http://www.box.net/shared/3t5u4m1qp2nib2kfmeye

I have to add more Employee later. If you can suggest an alternative way to stick all employee sheet in one sheet. I am open to this.

Thank you.

 

[AMAS]

This may be over simplistic, but have a look.

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

AMAS