Working out vacation

[mattp1980]

Hi all, first time poster here, so please be gentle

I have done a search and although I can find threads similar to my query, I can't quite get the answer I need.

I currently have a spreadsheet set up at work to show each employee's sickness and vacation records. The vacation days are where I have a problem. The way it is currently operating is that I have entered the employee's starting date, I have a formula in place to work out how long that employee has worked for me based on their start date. An employee begins to accrue vacation time from the day they start.

The current company policy is anyone who has worked here for under 5 years, is entitled to 20 days vacation per fiscal year. Anyone who has worked for 5-10 years get 25 days and anyone 10 years or more gets 30 days.

My current setup will look at the length of service that I mentioned earlier and determine whether that person gets 20, 25 or 30 days vacation.

Now the tricky part. I would like it (in a separate cell) to be able to show me how many days each employee has accrued from the start of the current fiscal year (by fiscal year, I mean it can literally be 1st April to 31st March. The exact fiscal dates are not essential).

I’m not sure what would be the best way to work it out, week by week (preferably) or month by month.

If anyone has any suggestions, it would be greatly appreciated.

Thanks
Matt

[DonkeyOte]

It would be a good idea to post a sample that reflects the set up of your data.

In reality - assuming you have a cell which as you say returns annual vacation for the current fiscal then accrued is basically going to be days worked in current fiscal / 7 rounded down to nearest whole number * annual vacation allowance.

Where you have current year starters things will be a little more convoluted as you need to either

a) apportion vacation days (ie 20 becomes 1-20 based on start date in relation to beginning of fiscal)

or

b) leave vacation days as 20 but adjust the days worked in current fiscal year such that it takes current date less start date (rather than fiscal start)

[mattp1980]

It would be a good idea to post a sample that reflects the set up of your data. *snip*

Hi DonkeyOte,

Thankyou for your fast reply. Assuming I have done it correctly, I have attached a screenshot of my spread sheet (each employee has their own sheet within the workbook, but the mechanics are the same)

As you can see from the image, each employee has a cell for every day of the year. Within that cell, I enter the amount of hours that person worked on that day, or whether they were sick or on a day off, etc. I then get a cumalative total for their work in year in the lower right corner. I chose this particular employee as they started part the way through the year.

What I am trying to achieve is in between the "Holiday entitlement" and the "Holidays taken" if to get the holidays accrued so far throughout that year. Once I have achieved this, I can adjust the "Holidays remaining" cell accordingly.

I hope this makes sense to you.

If the image isn't very clear, I can upload it to photobucket and provide a link to a higher resolution image.

Cheers
Matt

[DonkeyOte]

mattp1980 - not been online I'm afraid.

Much better to post a file than an image.... dummy names etc... and also outline expected result based on sample.

It would I think also help if you outlined which of the prior suggestions a) or b) you wish to adopt when calculating accrued vacation for this with less than 1 year service (ie current year start)

[mattp1980]

mattp1980 - not been online I'm afraid.

Much better to post a file than an image.... dummy names etc... and also outline expected result based on sample.

It would I think also help if you outlined which of the prior suggestions a) or b) you wish to adopt when calculating accrued vacation for this with less than 1 year service (ie current year start)

OK, I will make the adjustments to include dummy names, etc. and will post it. I do have a rather busy shift ahead of me tonight, then after tonight is over, I am not in work for 8 days so it will most likely be when I get back.

Thanks for your help so far, it is very much appreciated.

Regards
Matt