Vacation Earning Rate and Vacation Taken Total

ScottB

Hello,

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.

Thank you!

Paul Newcome

I think it should be working just fine to include when we move into a different month. The SUMIFS is pulling from the appropriate column, but then it adds each row that has a [Month] less than or equal to MONTH(TODAY()).

So right now the formula is only pulling in row 1. Starting Feb 1, the formula will pull in rows 1 and 2 and add them together.

Paul Newcome

Are you able to provide screenshots with sample data for context?

ScottB

Smartsheet PTO Scenario.docx

Please see attached, Paul. My second explanation may be more confusing than my initial submission. :)

I appreciate any input you can provide. Thank you.

Paul Newcome

[Vacation Hours Used] would be a SUMIFS that includes a date range to keep it limited to the month in the consolidation sheet.

The accrued would require more context. I see in the second screenshot you have months listed in a column. Does that remain true for every year, or is there a year factor (2023 vs 2024) that needs to be included? Are we just pulling from the appropriate column based on tenure and then the appropriate row based on today's date? How does all of that factor in?

ScottB

The accrual numbers will not change from year to year, Paul.

Also, I provided the wrong information earlier. Vacation hours are accrued once at the beginning of every month. So, looking at the 'Accrual Schedule Sheet' earlier, Joe Schmoe needs to have 10 hours of vacation added to his cumulative vacation hours on February 1st, given he is in the 'Days Earned - 17 Years' category in the example, 12 hours of vacation added to his cumulative vacation hours on March 1st, etc. I created the 'Years Worked Category' (third screenshot) as a placeholder, but I don't have a link between the employee's tenure and the 'Days Earned - ##Years' table at this point, so I'm open to suggestions.

Thanks again for assessing!

Paul Newcome

In the Accrual Schedule sheet, insert a text/number column and manually enter the month number in each of the rows. Then you would use a SUMIFS with an IF statement to generate the range to sum and then the range/criteria set for filtering would be this new column with the manually entered numbers being less than or equal to MONTH(TODAY()).

=SUMIFS(IF([Years Worked Category]@row<= 10, {10 Year Column}, IF([Years Worked Category]@row<= 15, {15 Year Column}, ..........................)))))), {Helper Number Column}, @cell<= MONTH(TODAY()))

ScottB

Ok Paul. I'm close, given your input, so thank you. I have one more hurdle.

If you look at the following first screenshot, '10.5' hours is being pulled in to 'This Month's Vacation Hours Accrued' column based on the current month being January and the employee's 'Years of Service' being 17 (see second screenshot).

'Vacation Hours Used' is a total pulled from a separate sheet that tracks vacation usage starting January 1st of 2024 (again, thanks for the info).

'Current Vacation Time Available' of '148.5' is the total hours available given the 'Starting Pt.....' hours + 'This Month's Vacation Hours Accrued' - 'Vacation Hours Used'.

Here is my predicament. 'This Month's Vacation Hours Accrued' column is pulling this month's '10.5' hours given 17 years of service. In February, an additional '10.5' hours will be accrued and needs to be added to the 'Current Vacation Time Available' total. I'm not sure how to automate adding each month's newly accrued vacation time to the total. 'This Month's Vacation Hours Accrued' cell has January's '10.5' hours indicated. If you look at the second screenshot, '10.5' hours are earned in February as well, so that number will not change between January and February regardless of new hours being accrued. Those hours do change from '10.5' hours to '11.25' hours in March. I thought I may be able to use 'when cell changes' copy into' automation, but as indicated, that '10.5' won't change between January and February. In the end, I need each month's newly acrrued vacation hours added to the 'Current Vacation Time Available' column.

Let me know if that makes sense. Again, thank you very much for your help. I sincerely appreciate it.

Paul Newcome

I think it should be working just fine to include when we move into a different month. The SUMIFS is pulling from the appropriate column, but then it adds each row that has a [Month] less than or equal to MONTH(TODAY()).

So right now the formula is only pulling in row 1. Starting Feb 1, the formula will pull in rows 1 and 2 and add them together.

ScottB

I'll see how things go on Thursday morning when February hits. Thanks again, Paul. Much appreciated.