#### cplusHR

##### New Member
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.

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).

#### cplusHR

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

#### AMAS

##### Active Member
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

#### MikeWx

##### Active Member
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

##### New Member
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.

#### cplusHR

##### New Member
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

##### New Member
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 ---

#### cplusHR

##### New Member
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>

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.

Replies
3
Views
359
Replies
0
Views
214
Replies
1
Views
156
Replies
3
Views
795
Replies
7
Views
2K

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

### 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.

### Which adblocker are you using?

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

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