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

[AdeT]

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.

 

[earthworm]

One Solution

Book1
	A	B	C	D	E	F	G	H	I	J
1	Length of Services (Month)	Sick Leave (Days)	Hospitalization Leave (Days)		Hire Date	3 Months	1 Year	Current DATE	Working Days	Entitlement
2	3	5	10		29-Aug-20	29-Nov-20	29-Aug-21	29-Nov-20	92	Entitled for SL
3	4	8	22		30-Aug-20	30-Nov-20	30-Aug-21	29-Nov-20	91	Not Entitled for SL
4	5	11	34
5	>=6	14	46
Sheet1

Cell Formulas
Range	Formula
F2:F3	F2	=EDATE(E2,3)
G2:G3	G2	=EDATE(E2,12)
I2:I3	I2	=H2-E2
J2:J3	J2	=IF(H2<F2,"Not Entitled for SL","Entitled for SL")

 

[AdeT]

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]

You need to build a calendar just like attendance sheet to calculate sick leave.

Example

Book1
	A	B	C	D	E	F	G	H	I
2	Name	Total SL Taken	01-11-21	02-11-21	03-11-21	04-11-21	05-11-21	06-11-21	07-11-21
3	Employee A	1	SL
4	Employee B	2		SL		SL
5	Employee C	3					SL	SL	SL
6	Employee D	1							SL
7	Employee E	4		SL	SL		SL		SL
Sheet1

Cell Formulas
Range	Formula
B3:B7	B3	=COUNTIF(C3:I3,"SL")

 

[AdeT]

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]

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]

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]

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]

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