Vacation tracking from an anniversary date from year to year

**alejandra92963** · 01-13-2016, 01:12 PM

Of course...I am flexible as far as how to input, but she isn't on what she wants. I feel like I should bake you cookies or something for this kind of work

**tony h** · 02-04-2016, 03:15 PM

Rather than use the multiple sheets and books. Have you considered using a single book for all years and a sheet where you select the year and month with a side panel for the annual summary?

**alejandra92963** · 02-04-2016, 05:21 PM

I am willing to do whatever works...I just need to have one sheet that I can print each month that breaks it down for the lady that does the payroll. I am kinda confused by how I could do what you are asking though. I understand making a running sheet for the whole year, but how would I calculate from anniversary date to anniversary date that way? And I am going to show my blonde with- What's a side panel?

**tony h** · 02-04-2016, 08:07 PM

It has gone midnight here and the laptop is running out of power. I'll try and give it a go tomorrow to see if the concept will work.

**tony h** · 02-07-2016, 08:53 PM

Right.

Note : I am English so my dates are dd/mm/yy ie 30/01/2016 is 30th Jan.

This is a first try - a bit rough and ready - but you can see the principles (I hope).

I haven't put anything in to stop you messing it up so please read these notes

First: there are two sheets: Data and View

data contains a list of holiday dates with the number of hours taken on each occasion. To record and change holidays add or remove from the list. As you can see they just get added in no particular order.
Adding more data works.
The same date can be used more than once for a person. 2 days holiday would be added as two entries - one for each day.

View is a monthly view and an annual summary of leave booked.

The Green boxes are the year and month being shown (you can change the year and month this works) (there is data for Jan and Feb 2016)
Note the month (at the moment) is always 31 days - this can be corrected.

The Start Day and month(under the yellow area) are the start anniversary dates for holiday for the people (Changing the start day and month works)

The colouring of the cells is done on format rules.

The staff names ang to h can be changed and used in the holiday list on the data sheet

You can add data for any years starting in 2015 and ending in 2018 - just because these are the dates I set up in the list.

Let me know what you think.