Request Time Off Tracker Help

Tyler12345

Hello,

My story seems to be familiar to others, but no past post seems to have a good solution. I've been searching the community forums here for a request time off tracker template (found a couple none actually worked), ended up creating my own. I got the majority of the automation and formulas worked out but I'm stuck on trying to add in the hr accrual rate based if a specific date has been reached.

Specific date = each pay period, as each pay period we accrue 3.33hrs of PTO (this is the value I need to get added back into the total PTO time remaining value).

My workflow goes like this:

1. Employee goes to their own form (screenshot 1), fills out info and submits
2. Form sends 2 notifications out (HR Manager and Employee Approval Manager).
   1. HR Manager can approve or deny, their way a notifications is sent out to the employee letting them know status of approval.
3. Main PTO sheet tracks employee requests and main info (screenshot 2)
4. Helper PTO sheet tracks extras (holidays, accrual rate, PTO Start Excel (value used to ingest accumulated PTO hours from old tracking system), Start Accrual and End Accrual Running list of pay period dates, figured I could use this to do a vlookup of shorts to add the accrual rate back into the Total PTO remaining value). Didn't work out well (screenshot 3).

Again, the only question I have is how can I get the 3.33 hr accrual value added back into my PTO Hrs Remaining cell value and have it continue add in the time so when the employee requested time off the values displaying in the notification are a true representation of PTO hours remaining.

Formulas I'm using on Main PTO sheet Screenshot 2:

1. Workdays Column: =NETWORKDAY([Start Date]@row, [End Date]@row, {PTO List of Holidays Range 1})
2. PTO Hrs Used: =SUM(Workdays@row * 8)
3. PTO Hrs Remaining: =SUM({PTO List of Holidays Range 2}, -[PTO Hrs Used]@row)
4. PTO Calc: =[PTO Hrs Remaining]@row

 

Genevieve P.

Hi Tyler,

What if you set up a completely new sheet (another helper sheet) with the exact same columns as your main PTO sheet. Then, have a date column that only contains the first of each month, and ensure each employee has one row per month that states 3.33 in their PTO Accrual Rate column.

Next, you could create a workflow that Moves the row to your master PTO sheet once that date is reached. This would automatically add in a new row to this sheet that has 3.33 hrs on the first of the month (and 0s or blanks in all the other columns).

Finally, adjust your PTO Hrs Remaining formula to include in the Accrual Rate column's calculations, so that at the start of each month this total formula will add in 3.33.

If you are worried that your original workflow will trigger sending out an Approval Request when rows are moved at the beginning of each month, you could create a condition in that workflow that says it only runs if the Accrual Rate column is blank.

Let us know if you think this will work!

Cheers,

Genevieve

Tyler12345

Hey Genevieve,

Thanks for the reply, I was able to fix the above problem on my own, but now I'm hitting another different problem. Will post a new question.

Thanks for the help,

Tyler