Complex Vacation Accural Formula/Script needed

[bioviper]

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?

[aydeegee]

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

[bioviper]

Vacation Master Log.xlsxThank you very much for wanting to do so!

The workbook I have enclosed is "Vacation Master Log". It has two sheet... the "Allocated" which lists all the current employees, and vacation accrued based on hire date. Then there is "Import" which will act as the Response Form when people submit a request. The Google Form is a reference to let you know that the data in "Response Form" is added in via web using Google Forms.

See if there is anything that can be done. Keep in mind this spreadsheet exists on our company's google drive.. which means its a live form. That is how Google Forms is able to log responses to it as requests come in.

P.S. There is no carry over. At the anniversary date, any unused vacation time is reset. You can look at the formula for "Vacation Days" column to see what how many days is available based on # of years in the company. The longer they been there, the more days the have at start of the anniversary.

[aydeegee]

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 )

[newdoverman]

Something like this formula used in Allocations!F2 and copied down should do what you need. The formula needs your cell references for the leave credits.

=if(INDEX(Import!G:G,MATCH(Allocations!A2,Import!B:B,0))="approved",subtract amount approved,"not approved")

As a side note, I think that subtracting the leave upon approval and before the leave has been taken is a mistake. I kept track of leave for over 30 employees for more than 10 years and quite often the vacation dates had to be adjusted either by the applicant or the employer. Operational requirements of the company can change drastically very quickly so giving final approval for leave too soon can cause some very big headaches for everyone.

I think that you would be better off in the long term if you can get access to a relational database for this as it handles these problems much better and has the capacity to handle much more data. (IMHO)

[bioviper]

Thank you so much for assisting.

1- the imports sheet doesn't get archived. It can simply contain all requests as we are not a huge company.

2-Allowance is synonymous to vacation days available. We would ask employees to put the total days they have allotted at the time of submitting their request. Although its redundant, my manager requests to have it on there.

3- the email address can be used as a reference.. Ultimately it's Google Forms that logs this address at the time of submission. Alternatively, we could create a drop-down on the form where the employee just picks their name...so that way an email address doesn't even need to be used. The email would be constant... They can only use this Vacation Request Form if they are logged into the company email address, which everyone has only 1.

As the other member mentioned... The approval would be tricky if the employee does the request but doesn't take the days. However, since its a live data sheet, the manager can go back to the entry and just put Not Approved which I assume would null the calculation; restoring the lost days, correct?

[aydeegee]

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

[bioviper]

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!

[newdoverman]

Aydeegee, your concerns are well founded.

[aydeegee]

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

[bioviper]

Excellent, thank you so much.

After testing the sheet, I have an issue:

When a request is submitted, after it goes to the "Import" sheet, it does not tabulate columns H, I, J (the ones with formulas built it).

The requests come in from a Google Form which links its responses to this sheet. When response is submitted, it appears that it just creates a new row on "Imports" sheet and does not carry down the formula. Right now I had to manually 'drag' the formula from the existing requests on to the new row to get it to formulate. Any work around this?

It shows all the other data such as username email, dates requested, etc (which the employee would type in the Google Form) and those display in their respective columns properly.

[bioviper]

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!

[aydeegee]

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

[aydeegee]

Hi bioviper

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

Regards
Alastair

[bioviper]

Done : ) and thank you.

[aydeegee]

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

[aydeegee]

(Duplicate message deleted)

[bioviper]

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.

[bioviper]

(duplicate post)

[aydeegee]

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

[aydeegee]

Hi Bioviper

As per PM here is a revised sheet

Regards
Alastair

[bioviper]

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