# Calculating Sick/Personal Leave

1. ## Calculating Sick/Personal Leave

My manager has asked me to set up a timeroll speadsheet for 2013 because we currently keep track on paper. I currently use Excel 2010 I have set up a workbook for all full time employee. Within this workbook I have figured out my formulas for days earned which will decrease as days are used. All employees get 15 sick days a year and out of those 15 days 3 can be used as personal days. I am very confused as how to continue my formual to show if a day of personal days are used this also need to be subtracted from sick days. Below are my current formulas for sick and personal days starting in January 2013. I have also attached my workbook for better understanding.

B6=(c6-aj6)/8
Sick Days Remaining=15 sick days a year-sick days used for the month
B7=(c7-aj7/8)
Personal Days Remaining=3 Personal days a year-personal days used for the month

Thank you,

2. ## Re: Calculating Sick/Personal Leave

1)The leaves count as "hours", and each 8 hour treat as one day used.
2)Each month uses a sheet, there will have situation a month record will use last month's result.

Here is what I suggest, you can have a try to let me know if it works.
In Sheet "Jan 2013"
B6:
B7:
In Sheet "Feb 2013"
B6:
B7:

3. ## Re: Calculating Sick/Personal Leave

This works however it only subtracts if an employee takes personal or sick days indvudially. I am more looking for if a personal day is used this needs to be subtracted from the personal days remaing and also from the sick days remaing as we have 15 sick days and 3 of those days can be used for personal. There has to be a way to extend my formula in my Sick Days remaing cell (B6)to subtract if any of personal days (B7) were used.
Thank you

4. ## Re: Calculating Sick/Personal Leave

Do you mean, if personal days are used BUT there's no personal day remains THEN sick days will be subtracted right?

If that's the case, then:
In Sheet "Jan 2013"
B6:
B7:
In Sheet "Feb 2013"
B6:
B7:

5. ## Re: Calculating Sick/Personal Leave

I mean if personal days are used then sick days will also be subtracted. Employees have 15 sick days a year an employee may use up to a maximum of 3 days per year from their annual sick leave as personal days. I somehow need to extend my Sick Leave remaining balance cell B6 to also subtract if any personal hours were taken.
Cell C6=120 hours of yearly sick leave(Cell B6=15 days(converted hours to days)):
Cell C7=24 hours of personal leave (B7=3 days(converted hours to days)):if these three days are used througout each month they need to be subtracted from C7 and also from the C6.
Note: 8 hours = one day

Thank you,

6. ## Re: Calculating Sick/Personal Leave

So, you want a return of both remaining sick/personal day and their hours?

I think my code in #4 can suit the need as it has considered the done used of personal leave effect on sick leave.
Try it.

7. ## Re: Calculating Sick/Personal Leave

This does work now I must have orginally typed something incorrectly. - Thank you Nels for taking the time to help me.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1