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

AdeT

New Member
Joined
Nov 19, 2021
Messages
5
Office Version
  1. 365
Platform
  1. MacOS
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.

1637302469794.png
 

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
Joined
May 19, 2009
Messages
690
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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")
 

AdeT

New Member
Joined
Nov 19, 2021
Messages
5
Office Version
  1. 365
Platform
  1. MacOS
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

1637547779113.png



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
Joined
May 19, 2009
Messages
690
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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")
 

AdeT

New Member
Joined
Nov 19, 2021
Messages
5
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

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
Joined
May 19, 2009
Messages
690
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
 

AdeT

New Member
Joined
Nov 19, 2021
Messages
5
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

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
Joined
May 19, 2009
Messages
690
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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.
 

AdeT

New Member
Joined
Nov 19, 2021
Messages
5
Office Version
  1. 365
Platform
  1. MacOS
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

1638257921925.png
 
Master Excel Bundle

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.

Forum statistics

Threads
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?

Disable AdBlock

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
Top