Recently discovered Airtable and love what it can do as I track several things across a good amount of Google Sheets. I have been wanting to consolidate them for a long time and Airtable looks the part. I have a very long and complicated if/else statement that I need help with. I have never written script before and over the last three days I have tried to learn as much as I can about it. With that being said it is still over my head on how to accomplish what I want. I tried to do this with a very long nested if statement with no luck and after some research it looks like a script would easily do want I need. Maybe someone smarter than I can get it done with a formula instead of a script. Just not sure what is the best and easiest way to get this done.
Our company pays out a year end profit share bonus based off the number of training hours the teammate(employee) completes. The basic formula is long but fairly easy to follow. Where things get complicated is if the teammate joined us during the current year. Last year we expanded and had several join us throughout the year. Every quarter we require a minimum of 10 training hours and if the teammate does not get the minimum in any quarter no bonus is paid out. I have four fields in the table that rollup the training hours that are inputted in another table. I also have a filed that will roll up all the training hours for the year.
Basic formula:
If the teammate completes 10 hours each quarter and then the total training hours is 80 hours or above the hourly bonus =1.00
If the teammate completes 10 hours each quarter and then the total training hours is between 60 hours and 79.9 the hourly bonus =0.75
Finally if the teammate completes 10 hours each quarter and then the total training hours is between 40 hours and 59.9 the hourly bonus =0.50
Complicated by hire date is in the same year
If the hire date is in the 1st quarter we do not start the training hours requirement until the 2nd quarter meaning they only have to complete 10 hours in the 2nd, 3rd, and 4th quarter.
If they do that and they have a total of 60 hours or above the hourly bonus is 1.00
40-59.9 hours is .75
30-49.9 hours is .50
Hire date is in the 2nd quarter then the have to do a minimum of 10 hours in the 3rd and 4th quarter to be eligible.
If they do that and they have a total of 40 hours or above the hourly bonus is 1.00
30-39.9 hours is .75
20-29.9 hours is .50
Hire date is in the 3rd quarter then they have to do a minimum of 10 hours in only the 4th quarter to be eligible
If they do that and they have a total of 20 hours or above the hourly bonus is 1.00
15-19.9 hours is .75
10-14.9 hours is .50
If their hire date is in the 4th quarter they are not eligible.
Here is a screenshot of the base with the table and field names
There maybe an easier way to do what I want and if there is it is outside of my experience. If someone would be so kind to help me with this I will be in your debt. I am just stuck at this part and after several days looking at attempting to learn some JavaScript my brain is mush.
