Vacation Spread sheet

[ehdz0222]

I need to create a vacation spreadsheet and I need major assistance. Our employees vacation is by anniversary date and I would like the vacation to reset when each anniversary date rolls around automatically.

I have the employee names and DOH. I need to keep track of dates taken and for what, whether vacation, personal or Sick leave, bereavement, etc.

Below are the variables:

After 1 year the employee earns 5 days. Zero vacation from DOH until after first year.
Year 2-4 employee earns 10 days
Year 5 and after employee earns 15 days

At day 61 each employee earns 3 personal days and 3 sick days.

All must be taken within year earned or they loose them.

If anyone can help me I would greatly appreciate it.

[newdoverman]

Welcome to the forum.

Please upload a mock-up of a workbook showing how the above is anticipated to work.

I'm confused by the 61 days. Is that every 61 days or just once per year or just once for their career. I take it that all the days earned are vacation days. Are there other days to be accounted for?

Are the days awarded as a lump amount at the beginning of the year after the anniversary date?

[ehdz0222]

Hi Newdoverman, thanks you for your response. I am attaching the excel sheet I have downloaded for use. It seems to have all I need with the exception of a few things.

Not sure how some of the functions on this sheet work.

- Col A - Some of the dates turn red if DOH is past the year etc.
- Col S - The button to add hours does not work, would be a nice feature but does not work.
- Col Y - I am not sure what the Days count in column Y is for.

As for your questions:

After initial DOH each employee receives at day 61 3 personal days and 3 sick days. If not used throughout the year these 3 days reset regardless on anniversary date, they do not roll over.

Vacation earned:
Yes to above question -
5 Vac days or 40 hours earned after first year anniversary and resets itself each year anniversary date
10 days or 80 hours earned after years 2-4 anniversary date and
15 days or 120 hours earned after year 5 anniversary date

Both vacation and personal/sick days should reset at anniversary dates.

[newdoverman]

Col A is Conditionally formatted to show people that have more than 1 year of service since Hire Date. This is unnecessary as you already have that data in column B Years of Service. Go into Conditional Formatting, Manage Rules, Workbook and delete all the rules then click APPLY then OK. This will get rid of the unnecessary colour coding.

The Blue Button:
Right click on the button (don't left click first just right click anywhere on the blue button). Choose Assign Macro and select and select Plaque3_Click then OK. That will assign the macro to the button.

Col Y appears to be the start of the calculation of the 61 days to get the 3 days Personal and Sick. A calculation like this will give you the number of days since "Anniversary Date". Any value >=61 give 3 days credit. (this is just a guess on my part for this incomplete calculation that is there.)

Please Login or Register  to view this content.

You have separate worksheets for VacationHours, PersonalHours, SickHours etc. I think that someone conversant in VBA could have the correct rows blanked on the anniversary dates so that the calculations on the Summary worksheet would be "reset". I'm not conversant in VBA to be able to do that.

It appears to me that VBA is the way to go and a VBA expert should be able help you out with that.