Please help!! Vacation Accrual Formula

cplusHR

New Member
Joined
Mar 19, 2011
Messages
25
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.
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

AMAS

Active Member
Joined
Apr 11, 2010
Messages
472
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.
Code:
=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
 
Upvote 0

MikeWx

Active Member
Joined
Jan 16, 2010
Messages
436
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?
 
Upvote 0

cplusHR

New Member
Joined
Mar 19, 2011
Messages
25
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.
Code:
=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.
 
Upvote 0

cplusHR

New Member
Joined
Mar 19, 2011
Messages
25
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.
 
Upvote 0

cplusHR

New Member
Joined
Mar 19, 2011
Messages
25
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 ---
 
Upvote 0

cplusHR

New Member
Joined
Mar 19, 2011
Messages
25
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-com:eek:ffice:eek:ffice" /--><o:p></o:p>

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.
 
Upvote 0

Forum statistics

Threads
1,187,107
Messages
5,961,633
Members
438,557
Latest member
Jadatutor

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top