Vacation tracking from an anniversary date from year to year

You seem pretty much there. Are you allowing carryover of vacation time from year to year?

I would use TODAY() instead of NOW() (NOW() includes a time which you don't need)

Once I have an answer to my question, we can tighten up your sheet. I might suggest another sheet in the workbook containing a running total with each line/row simply being employee name, date of vacation start, date of vacation end, total days. Then you can pull "Hours Used" from that sheet and not worry about math.

I had trouble coming up with a formula that would split networkdays if vacation started on one side of an anniversary and ended on the other so decided on a manual approach. If the user is entering the vacation dates on "Data" tab and it crosses the anniversary, the entry will turn red. Then the user should split the entry into two lines. First one up to but not including the anniversary date, the second one from anniversary date forward. (I did this on lines 2 and 4). Column F shows the anniversary date (updated to the year of entry).

I also included a Partial day column (D) where you put in hours of vacation on a partial day. This is inclusive of the dates in B and C. So on 10/21/2015, Employee A came in and worked 6 hours and then went home (2 hrs vacation)

Hope this helps. Questions?

Ugh! Because your data is split up by month, the formula will need to sum up each spreadsheet. Also, if the vacation time is accrued based on start date and not calendar date, then there will be carryover from workbook (2015) to workbook (2016). Right? Can we modify your spreadsheet setup?

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?

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.

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.

Welcome to the forum jferich.

Originally Posted by jferich

This is interesting. Is there an updated final version?

As this thread is approaching 6 years old it is not likely the original participants are still monitoring it.

I encourage you to start your own thread making reference to this one if it helps.

Since you are new you won't be able to post a link yet, but you can copy/paste the title.