Need to create a vacation tracker for 350+ employees

We currently track vacations in three ways - a book with a sheet in it for each employee, a calendar in email for making sure people aren't overlapping, and the physical request form for making sure employees get paid at the right time for their vacation. I am trying to combine all these functions into one spreadsheet (since we don't have any kind of HRIS program) and I'm getting stuck, even when I give up trying to get the last one in.

I'm using the Employee attendance tracker template, and it's almost exactly what I want, in that it shows how many vacation days are used, how many are left, and has tabs for each quarter so individual days can be marked off (which combines the first and second functions into one). HOWEVER, our employees vacation resets on their anniversary date, not a calendar, so it's not working as is.

Does anyone have suggestions for tracking vacations by anniversary date in Excel? I tried adding extra tabs for previous and future quarters and noting the hiring date, but I run into problems when an employee schedules time far in advance, after their anniversary date. (Ex. it's July, an employee resets in September, and submits requests for August and November.)

I'm in luck (I suppose) in that I'm not worried about accrual or any other kind of leave, but I fear I'm stuck with paper forever since we have no budget for software. I attached what I've got so far (with dummy information), and any suggestions would help.

The time is given in full to each employee on their anniversary date, no accruing required. But everyone's date is different, which is where I'm running into trouble.

Also I'm not worried about computing conflicts or anything like that - I can look at the individual dates when I'm putting them in and see that there is or isn't an issue.

I think i have the same problem, but for a different type of issue. Take a look at my question here:

http://www.excelforum.com/excel-prog...nce-issue.html

But i think for something as critical as what you are talking about newdoverman is 100% correct. Excel is not the program for something this critical. Just my opinion.

Haha, I might be able to get some software for it if paper magically stopped working somehow, but until that happens, I have to work with the (free) tools I already have.

Thanks for that. I took a look at it and it seems like that's a way to count the number of vacation days they start with. What I'm looking for is a way to count the number of days they have left, taking the hire date in column E into effect. AKA, only count the marks in date columns on other sheets if it's before the date in column E.

To expand further:

If someone's hire date is 5/16, I would want it to count only the marks in columns on the 2013 Q2-2014 Q2, 5/16-5/15. My guess is that's probably overly complicated, and is probably going to require modification for each individual column C formula on all the non-YTD tabs.

So my question is: do I just need to individually modify each column C formula on each tab, and then modify the YTD column C so it only grabs the relevant quarters?

Yeah, the process of adding employees in is going to be annoying, since I'll want to keep them in alphabetical order.

Your V vs PV idea is good. I may have to add another column on the summary page to account for those, as I have people who will schedule blocks of time in advance and then still leave open the possibility of taking additional time between now and then, so I'll need to know how many scheduled days they have as well.

Thanks for your assistance. I'm going to try downloading all the employees into the sheet and see how this scales up to 350-400 employees.

Thanks to every one. I was wondering to find attendance template for my store employees finally i get it.