PTO Accrual Data

[chrisnewcity]

We want the worksheet to show us how much PTO an employee has accrued based on the pay period chosen from a drop-down list.

Employees are paid on the 15th and 30th of each month (February is 15th and 28th).

Each pay period, the (full-time) employees get 4 hrs.

At the end of each quarter, employees of 5-9 years receive an extra 8hrs and employees of 10+ years receive an extra 16hrs - - in addition to the normal 4 hours, e.g. 3/30, 6/30, 9/30, and 12/30 accruals will either be 4, 12, or 20.

An employee begins getting their extra tenure hrs at the end of the quarter following their work anniversary.

We want the summary to simply list the employees in Col. A and their respective accrual in Col. B, but the accruals need to change based on the selected pay period (drop down list).

Question 1: how should we organize the data?

Question 2: what is the formula for displaying the accrual?

Sample sheet: docs.google.com/spreadsheets/d/1XyFocWYKkfTqg3upIJ8TTu3Zp27z2BAgU5C6tWA82T8/edit?usp=sharing

Thanks for your help.

[chrisnewcity]

Irrelevant as of 12/20 post.

[chrisnewcity]

So, I had an epiphany last night. Instead of trying to calculate 26 different drop down selections, I went with just 2: the hrs earned each period and the accrual.

I put the earned hours data on a place holder sheet and create results on the front page using a SUMPRODUCT inside and IF function.

The drop down toggles between the earned hours each period and a running total. The SUMPRODUCT function uses named ranges and conditionals.

I have added the solution sheet and placeholder data to my original problem sample sheet for comparison.

If anyone has a more elegant way of doing this, please feel free to respond.