Vacation Accrual and Deductions

BFuller

Hello,

I need to customize a vacation tracker to our company and all the tutorials I've watched don't seem to match our unique situation and I'm hoping someone can help me with these formulas.

Employees accrue hours off per month, which are credited to them at the start of the month. For example, someone may get 10 hours a month for a total of 120 hours a year while a more tenured employee will get 16.667 hours a month for a total of 200 hours a year. And, a new employee will only accrue 6.667 hours a month for a total of 80 hours a year.

Ideally, I would like a form that, when the hours requested off is summited and approved, it subtracts of the total amount available while that same sheet also credits each employee as the new month begins. Can someone help me? Thank you in advance!

Antonio Figueroa

So here you go.

In the Qty Hrs Per Month column put the qty of hours to receive per month.

in the today column write this formula. =today()

in the value of month column write this formula. =value(left(today@row,2))

in january write this formula =[qty hrs per month]@row

in february write this formula

=IF($[value of month]@row > 1, $[QTY HRS Per Month]@row, "")

Copy this formula all the way across to december.

then in march formula change the number from 1 to 2

=IF($[value of month]@row > 2, $[QTY HRS Per Month]@row, "")

then in april change the 1 to 3

may to 4

april to5

continue till december.

in year total write this formula =SUM(January@row:December@row)

then in the Hours Remain write this formula =[Year Total]@row - SUMIF({Hours Vacation Range 1}, Name@row, {Hours Vacation Range 2})

Hours Vacation Range 1 you will have to change to your vacation sheet that your form is on. and that will be the name column

and the Hours Vacation Range 2 you will have to change as well to the hours on the vacation sheet.

Hope this works

Antonio Figueroa

You can Hide all the columns that you dont want to see after you set this up.

and you can try it out by changing the date in the today's column but make sure you put the formula back in when you go to use it.

BFuller

Thank you so much for this. I will test it out. Thanks again!

Lisa Wood

@Antonio Figueroa I am working on a similar solution, with hours accrued quarterly. I am not quite grasping how to have a form where users submit time and it connects to this sheet as you describe. Can you elaborate on this part a bit more?

"Hours Vacation Range 1 you will have to change to your vacation sheet that your form is on. and that will be the name column

and the Hours Vacation Range 2 you will have to change as well to the hours on the vacation sheet."

I understand the first part, but not the second. Thank you!

DKing

Hi all,

I just started using smart sheet and am interested in this thread. Was there a follow up to Lisa woods question? I am good up to that part.

Thanks

Paul Newcome

There could be a much more simplified solution depending on if there is any rollover of the accrued hours or not that eliminates a large majority of the helper columns.

Either way I am going to suggest two separate sheets. One that has a list of employees, their start dates, how much total time should be accrued as of today, and how much total time has been used as of today.

The second sheet would be the request form that simply intakes the employee's name (and/or id for a unique reference for each person), and the number of hours requested (at a minimum).

First sheet would reference the second sheet to figure out how much has been taken.

Lisa Wood

That's exactly what I ended up doing, Paul.

Paul Newcome

@Lisa Wood Glad to hear you were able to get that part working. How exactly are you handling the accrual?

DKing

I ended up using 2 sheets as you outlined. I added a column for employee start date, however one formula I cannot get right is the accruals based on start date. Using the formula in the year total Column of =SUM(January@row:December@row) works if an employee starts in Jan, however If an employee starts part way through the year, would you reccomend a simple formula to calculate this? I have tried a few, but they don't sum up well.

Lisa Wood

Our employees earn 5 days per quarter. The sheet automatically adds those days at the start of each quarter, and deducts what was submitted through the form (separate sheet). When someone starts partway through the year, we manually add the hours accrued for that quarter and it's automated from there.

Paul Newcome

@DKing You don't need all the extra helper columns. The accrual can usually be calculated directly from the start date. How much is accrued when, and is there any rollover and/or cap?

@Lisa Wood It looks like you are already tracking rollover, so you should be able to automate the YTD accrual based on start date using something along the lines of...

=(INT(MONTH(TODAY()) / 3) + 1 - IF(MOD(MONTH(TODAY()), 3) = 0, 1, 0)) - (INT(MONTH(IF([Start Date]@row < DATE(YEAR(TODAY()), 1, 1), DATE(YEAR(TODAY()), 1, 1), [Start Date]@row)) / 3) - IF(MOD(MONTH(IF([Start Date]@row < DATE(YEAR(TODAY()), 1, 1), DATE(YEAR(TODAY()), 1, 1), [Start Date]@row)), 3) = 0, 1, 0))