Complex Vacation Accural Formula/Script needed

Hello All,

If anyone could assist me with my issue, I will be eternally grateful!

Here's the setup:

Our company has a Master Vacation Log (spreadsheet) that lists each employee, start date, years of service, and vacation days available. Employees, their first year, accrue a total of 5 days by their first hire date anniversary. At that point, they START with 10 days... and once again it will reset the following anniversary. Then they will have 15 days for year three.

I already have formulas to 'reset' the vacation days available based on anniversary date and years of service.

Here's what I need:

We have a Google Forms sheet that acts as a Vacation Request Form. Employee (using the google ID they have signed in) would pick the amount of days the want, reason, dates needed, and would submit it to a "Request" spreadsheet. You still with me?

The managers SHARE this "Request" sheet, and would then type/enter something in a cell next to the request to act as an 'approval'. Is there a formula/script that would recognize that once an 'approval' is done, to signal the Master Sheet to reduce the vacation days available, but still properly reset at the anniversary date? I would assume since there is a 'username' column, I could match it to the respective employee on the Master Log sheet to deduct the right persons days. We do this manually and I want to find a way to automate it. Keep in mind, the Vacation days available has a dynamic formula built in.

This has puzzled me for a very long time... any expert willing to tackle this for some major kudos?

Hi bioviper

Welcome to the forum

Am I correct in my assumption that the reference to Google forms was mere background information and that all we need to consider is the Request spreadsheet and the Master Vacation Log?

If I am correct, then could you provide a sample of the 2 spreadsheets (with all sensitive data removed) so that people can see what's involved?

Do we need to take into account any "carry over" from one year to the next?

I cannot spend any time on this for the next few days, but will pick it up if no one else responds.

Regards
Alastair

Hi bioviper

I have a few questions:

1 - What happens to the Import sheet? Do you keep all the data forever on this sheet, or is it archived?

2 - What is the significance of the "Allowance"?

3 - Can the email address be used as the reference, or do we have to use an intermediate? This would be necessary if the employees were sometimes to use their work email and other times their home email.

Regards
Alastair
(off to enjoy one of my 26 days per year holiday in my first year of employment. ) (+ 8 public holidays )

Hi bioviper

I attach my solution. Points of note:
- the system handles holidays irrespective of the anniversary of joining
- Allocations H2 must equal Import J2 - otherwise it means that something is not recorded correctly
- there can be many holiday requests by the same person
- Import column H shows the remaining balance (and will update if the request is approved))
- if a request is subsequently cancelled - just remove the approval "Y"

I would be extremely uncomfortable if I knew that my holiday system was open to all and sundry. Quite apart from any malicious intent - there is always the possibility of incompetence causing mayhem. I would consider locking all possible cells and taking regular backups.

Let me know if it works for you.

Regards
Alastair

Alastair,

Thank you so much for taking the time to create this! I am really grateful. I am going to test-submit a request to see if it tabulates properly. I see you incorporated a "Holiday Year Start" in column I. Is this to discern if an employee is requesting time off before or after their anniversary? I just want to understand the concept of Column I more if possible.

Thank you again so much!

Hi bioviper

You are quite correct in your understanding of the Holiday Year Start. The Holiday Year Start was used to determine whether or not a requested date falls within the Holiday year and thus has to be deducted from the allowance. So J4 does not count the requested days, as they belong to last year (and you said taht there was no carry-over). However, with a different start date (hence different anniversary date) the request maybe should have been counted.

Regards
Alastair

Update:

I created a "submission" sheet where strictly answers from the vacation request are inputted. Then I used the =importrange function to pull the necessary information to the "import" sheet. It looks like that did the trick! That way it doesn't override the H-J columns. : )

Alastair,

would you kindly PM me an address where I could send you a gift for your efforts? You have saved me considerable time and I would like to return the favor. Thanks!

Hi bioviper

I'm glad you got it to work. Until I read your Update, I was going to suggest making it into a table, then any entry after the las line would cause the table to increase. But you got how you need it and that is the main thing.

I'm not sure why we do spend so much time on this. In my case I'm too old for a paper round and there is nothing worth watching on the television. It's sufficient to get the kudos and the satisfaction of finding a solution. Having said all that, I believe that it would be churlish of me not to send you my address!

Regards
Alastair

Hi bioviper

Perhaps you will mark this thread as "Solved" (On your first post, edit the Title)

Regards
Alastair

Done : ) and thank you.

Hi Bioviper

I have re-worked the file. The comparison date should only be changed on Allocations Sheet J1 and NOT on Import Q1, which is only there to show the comparison date (this cell should be locked before others get their hands on it)

The helper columns Import J - O should be hidden.

Please test this (especially around the anniversary date) and see if it works for you, including changing the comparison date.

Regards
Alastair

(Duplicate message deleted)

So the comparison date is what appears to trigger anniversary date changes? I tried changing the 10/xx/2013 date you had to 8/26/2013 and a couple employees who's anniversary date was prior to that reverted to a 2012 date instead of 2013. I would like to know overall what were the changes you made to accommodate this in a live data base. Do we have to put today's date in constantly or just use the todays date function in the box and lock it?

I just want to know how o - j work in order to accomplish anniversary date changes in congruence with vacation days that span over them.

(duplicate post)

Hi Bioviper

First thing is to ensure that the dates are in the correct format. I know you Americans use the funny mm/dd/yy convention, but I used the far more sensible dd/mm/yy convention and may not have changed it back.

Do we have to put today's date in constantly or just use the todays date function in the box and lock it?

You can put in any date into J1. If you put in "=Today()" it will display the current date (and change tomorrow). If you want to change it to some other day, just type in any date (or press Ctrl+; to get today's date (that will not change tomorrow))

I did not have to change much, as the original version used "Today" to work out the anniversary date. I then amended it to look at the date in J1 (Allocations) to make the comparison.

I just want to know how o - j work

J takes the year from I and adds 1, takes the month and day. This avoids any problems of a leap year.
K checks that the start of the requested holiday is after the holiday year AND the end of the requested holiday is before the holiday year ends
L checks that the start of the requested holiday is before the holiday year starts AND the end of the requested holiday is after the start of the holiday year (ie the requested holiday clashes with the start of the holiday year)
M (does the same with the end of the holiday year)
N checks that the request is authorised and the holiday request is completely within the holiday year, then uses the full holiday requested
O checks if there is a clash and uses the amount of days in the current year (which is determined by the date in J1(

Keep asking if you want to understand further! (PM me for a Skype address)

Regards
Alastair

Hi Bioviper

As per PM here is a revised sheet

Regards
Alastair

Alastair. Thank you so much. I am testing the sheet now. Life has been too busy but I'll be in touch. Thanks mate : )