Sick Leave Accrued Formula?

[Henry_Falcon77]

Hello everyone. I work in a trade school have been asked to calculate is sick leave accrual for the staff. The company pays employees biweekly (40 hours each week, 80 biweekly, 8 hours a day. There is no limit to sick days carried over. I only need to show sick time accrued. I would like to be able to enter a start date and have excel show the total accrued hours available based on the info below.

Our policy is:

i. On January 1 of each calendar year, staff shall be credited with is sick time based upon their years of service, in anticipation of continued employment. example (if an employee started in June 2018, he would get 12 days sick time on January 2019.

Year 0-5 = 1 day per month
Years 6-12 = 1.25 days per month
Years 13-20 = 1.67 days per month
Years 21 forward = 2.08 days per month

Newly hired staff members accrue their first month of sick time according to their hire date.
Days 1-8 accrue 1 day of sick time
Days 9-23 accrue .5 days of sick time
Dates 24-31 accrue no sick time


So far I have the following information set up:
A2 Name
B2 Hire Date
C2 TODAY ()
D2 =(C2-B2)/7 (calculates the number of weeks since the employee was hired)
E3 I would like this to show the number of hours each employee has available to date.

I would greatly appreciate any help.

Thanks

 

[Logit]

Re: Sick Leave Accrued Formula? Grateful for any assistance

.
Looking at your information, how did you determine the new employee would accrue 12 days?

I determined 6 days.

What am I missing ?

 

[Henry_Falcon77]

Re: Sick Leave Accrued Formula? Grateful for any assistance

.
Looking at your information, how did you determine the new employee would accrue 12 days?

I determined 6 days.

What am I missing ?

In January of the following year, associates automatically receive the yearly accrual. So even if an associate got hired December 1st 2018, he would receive 12 days In January of the next year. Im trying to figure out how to display earned and accrued,

..Here is what a mean. Going back to my last example, the associate who got hired in December 2018, would accrue 12 sick days in January automatically. Im trying to figure out how many days he would have if he quit on a specific day like June 27th. Even though the 12 hours were accrued in January , they are not earned. So if an employee quit, he would not receive the 12 days he accrued in January and would instead be based on the months earned formula. Sorry of I didn't explain that well.

 

[Henry_Falcon77]

Re: Sick Leave Accrued Formula? Grateful for any assistance

.
Looking at your information, how did you determine the new employee would accrue 12 days?

I determined 6 days.

What am I missing ?

While looking though the four, I found something similar. Here is what I found.

"I have been tasked with developing a spreadsheet to track employee vacation accrual. The company pays employees weekly so I would like the spreadsheet to show the hours that each employee has earned.
Our vacation policy is:
0-5 years = 10 days (80 hours/year, 1.53 hours/week )
5 - 15 years = 15 days (120 hours/year, 2.3 hours/week )
15 years = 20 days (160 hours/year, 3.06 hours/week)

So far I have the following information set up:
A2 Name
B2 Hire Date
C2 TODAY ()
D2 =(C2-B2)/7 (calculates the number of weeks since the employee was hired)
E3 I would like this to show the number of hours each employee has available to date.

I would greatly appreciate any help.

Thanks

Someone replied with
=IF((TODAY()-B2)<365,((TODAY()-B2)/7)*1.53,IF(AND((TODAY()-B2)<=1825,(TODAY()-B2)>365),(((TODAY()-"1Jan"+0)/7)*1.53),IF(AND((TODAY()-B2)>1825,(TODAY()-B2)<=5475),(((TODAY()-"1Jan"+0)/7)*2.3),IF((TODAY()-B2)>5475,(((TODAY()-"1Jan"+0)/7)*3.06)))))
_________________________

I didn't know how to add the days accrued to the formula so I can calculate accrued vs earned time.

Newly hired staff members accrue their first month of sick time according to their hire date.
Days 1-8 accrue 1 day of sick time
Days 9-23 accrue .5 days of sick time
Dates 24-31 accrue no sick time

Thank you for your assistance Logit.