[SOLVED] Help with PTO/Vacation tracking

[jwr43920]

Hi
I am a beginner at Excel and have been given the task of tracking available PTO and Vacation/Personal hours for our company since the last person was let go. I have created a very basic sheet,but need help so that the sheet will know exactly what an employee has available as of their hiring date and will automatically adjust all necessary cells when a day is shown to be approved . The amount of days is not accrued , they are just rewarded after lengths of employment per the below list for salary and hourly.The time can not be rolled over, it is use it or lose it.Vacation days are capped at 15 days for hourly no matter how long you have been employed after the 10 yr mark.Any guidance would be greatly appreciated

Salary People Paid Time Off
Service Period
6months 2 days
First calendar Year 7 days
Anniversary Year 2 12 days
Anniversary Year 5 13 days
Anniversary Year 10 20 days
Anniversary Year 15 21 days
Anniversary Year 20 22 days
Anniversary Year 25 23 days
Anniversary Year 30 30 days


Hourly People Paid Personal Days
Service Period
6 months 2 days
Anniversary Year 5 3 days
Anniversary Year 10 5 days
Anniversary Year 15 6 days
Anniversary Year 20 7 days
Anniversary Year 25 8 days
Anniversary Year 30 10 days


Hourly People Paid Vacation Days
Service Period
1 year 5 days
2 years 10 days
10 Years 15 days

[mehmetcik]

Wow. You have had 460 views without a response.

This must be something worth getting involved with.

I will look at your file and get back to you.

[BlindAlley]

Here is a solution example for you. As always with Excel there are many different ways of completing a spreadsheet, Mehmetmik may come up with a completely different solution, I suggest looking at them both and picking the one that works for you.

Feel free to ask questions if you need to.

BA

[mehmetcik]

Ok.

I have assumed that the rows highlighted in yellow are the salaried staff.

you need to check that. Because some of them have used Personal Days which means that they are Hourly Paid.

In the First Table I have left your yellow lines untouched.


In the second table I have removed the Yellow lines and replaced that with conditional formatting.
This means that all the Salaried employees are highlighted as you update the spreadsheet.

[jwr43920]

Thank You very much. The yellow line was used to indicate that the days were accounted for with this particular individual, this all stemming from the company paying several employees over 10 days which they were not entitled to based upon the bad tracking of the previous individual.I will dig into these later today and really appreciate both of you taking time out to help me