Vacation Earning Rate and Vacation Taken Total
I am attempting to combine employee vacation time taken which I track in a separate Smartsheet with vacation time accrued each month based on a separate static 'earned rate' sheet. The 'earned rate table' contains each month of the year in the first column and accrued vacation hours in additional columns based on an employee's years worked. On specific dates twice a month, I need to automate pulling the applicable accrued hours from the 'earning rate' table into the 'vacation time taken' table to keep an updated total on remaining hours.
Per my generic table below, on January 10th (1st January pay date), Joe acquires 9.75 hours of vacation time as he has worked for the company 15 years. Michelle acquires 10 hours of vacation on the 10th as she has worked for the company 20 years. Both of them acquire their respective hours again on the 25th of January. As seen below, earned hours fluctuate each month and pay dates vary as well, meaning dates aren't on the 1st and 15, 1st and 3rd Wednesday of every month, etc. but I do have a complete listing of pay dates for the year that can be pulled into an existing sheet or used in a triggered automation.
10 years 15 years 20 years
Jan 6 9.75 10
Feb 6.75 9 11.25
I do not have the pay dates for each month or each employee's year worked in a table at this point, but I'd prefer to keep the 'earned rate' sheet static and place a 'years worked' field in the 'vacation time taken' sheet. Doing so would allow me to change the 'years worked' field as needed for each employee as time progresses and the new set of accrued hours would be pulled in accordingly.
I hope my scenario makes some semblance of sense. : )
Please let me now if clarification is required.
Help Article Resources
Check out the Formula Handbook template!