# Calculate sick leave and sick leave taken on hire date using excel

##### New Member
14 sick days per working year

If the Hire Date is for eg. 29/8/2020, then an employee has 14 sick days to use between 29/8/2020 and 28/8/2021. At 29/8/2021, the employee once again has 14 days. However if employee work less than 3 months, then he is not entitle to any sick days. I can't figure out what is the BEST calculation would be for it to automatically update each employee sick days entitlement per year. I also needs to calculate the sick days taken between 29/8/2020 and 28/8/2021 (Each employee has different date since hire date is not the same). Anyone has any idea how can this be done using excel ? Thank you in advance.

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

#### earthworm

##### Well-known Member
One Solution

Book1
ABCDEFGHIJ
1Length of Services (Month)Sick Leave (Days)Hospitalization Leave (Days)Hire Date3 Months1 YearCurrent DATEWorking DaysEntitlement
2351029-Aug-2029-Nov-2029-Aug-2129-Nov-2092Entitled for SL
3482230-Aug-2030-Nov-2030-Aug-2129-Nov-2091Not Entitled for SL
451134
5>=61446
Sheet1
Cell Formulas
RangeFormula
F2:F3F2=EDATE(E2,3)
G2:G3G2=EDATE(E2,12)
I2:I3I2=H2-E2
J2:J3J2=IF(H2<F2,"Not Entitled for SL","Entitled for SL")

##### New Member
I'm new to excel and thank you for your solution however how do I calculate the number of sick leave each employee entitle ?

1) Employee A works less than 3 months, 0 sick leave entitlement
2) Employee B works 3 months, sick leave entitlement will be 5 days
2) Employee C works 4 months, sick leave entitlement will be 8 days
3) Employee D works 5 months, sick leave entitlement will be 11 days
4) Employee E works 6 months or more, sick leave entitlement will be 14 days

I also needs to calculate sick leave taken for each employee during their sick leave entitlement start year and end year, is that possible ?

#### earthworm

##### Well-known Member
You need to build a calendar just like attendance sheet to calculate sick leave.

Example

Book1
ABCDEFGHI
2NameTotal SL Taken01-11-2102-11-2103-11-2104-11-2105-11-2106-11-2107-11-21
3Employee A1SL
4Employee B2SLSL
5Employee C3SLSLSL
6Employee D1SL
7Employee E4SLSLSLSL
Sheet1
Cell Formulas
RangeFormula
B3:B7B3=COUNTIF(C3:I3,"SL")

##### New Member

I had build a calendar to calculate sick leave however every employee sick leave entitlement start year and end year are not the same since it is calculated based on their joined date. Is there anyway to calculate the sick leave taken based on start year and end year ?

#### earthworm

##### Well-known Member
M
I had build a calendar to calculate sick leave however every employee sick leave entitlement start year and end year are not the same since it is calculated based on their joined date. Is there anyway to calculate the sick leave taken based on start year and end year ?
Apply post # 2 logic with post # 4

##### New Member

Hi earthworm.

Thank you for your reply however I don't quite understand how to apply the logic to calculate employee sick leave taken based on entitlement start year and end year.

#### earthworm

##### Well-known Member
J
Hi earthworm.

Thank you for your reply however I don't quite understand how to apply the logic to calculate employee sick leave taken based on entitlement start year and end year.

Just subtract entitlement from leaves taken.

##### New Member
Hi earthworm,

Sorry for not being clear on my question, I do not know how to calculate the employee sick leave taken based on below date range.

Employee A, what is the sick leave taken from 09-Oct-21 till 08-Oct-22
Employee B, what is the sick leave taken from 02-Aug-21 till 01-Aug-22
Employee C, what is the sick leave taken from 09-Jul-21 till 08-Jul-22
Employee D, what is the sick leave taken from 05-Jun-21 till 04-Jun-22
Employee E, what is the sick leave taken from 29-Aug-21 till 28-Aug-22

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,128
Messages
5,835,564
Members
430,367
Latest member
glastonbury

### 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.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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