# Sick Leave Accrued Formula?

#### Henry_Falcon77

##### New Member
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

### Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

#### Logit

##### Well-known Member
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

##### New Member
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

##### New Member
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.

Replies
0
Views
105
Replies
2
Views
144
Replies
1
Views
272
Replies
2
Views
182
Replies
17
Views
342

1,195,666
Messages
6,011,043
Members
441,580
Latest member
BornholmerBjarne

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back