Hi,
I need to prepare a tracker to monitor attendance. This requires us to check the number of times a person has taken sick leaves. If the sick leave is carried over a weekend, ie, fri through monday, the number of days of sick leave is 2 while the instance of sick leave is 1.
For eg:
Date 1 2 3 4 5 6
Day Fri Sat Sun Mon Tue Wed Number of leaves Instances
PD M M M 3 1
In the above eg, I have taken leaves on Fri, Mon and Tue, with Sat/Sun being weekends and non working days. Therefore total number of leaves taken = 3 and number of instances of leaves = 1.
I have used the below formula to calculate the instances of leaves :
=SUMPRODUCT(--ISNUMBER(MATCH($A1:$F1,{"M","MH"},0)),--ISNA(MATCH($B1:$G1,{"M","MH"},0)))
However, as the above does not discount the weekends, the result for instances = 2.
I need assistance to arrive at a formula that will not count the weekends. Some departments have Fri and Sat as weekends and therefore it needs to be flexible enough for us to amend the days that need to be discounted.
Any assistance that can be provided is appreciated as always.
Thank you again.
I need to prepare a tracker to monitor attendance. This requires us to check the number of times a person has taken sick leaves. If the sick leave is carried over a weekend, ie, fri through monday, the number of days of sick leave is 2 while the instance of sick leave is 1.
For eg:
Date 1 2 3 4 5 6
Day Fri Sat Sun Mon Tue Wed Number of leaves Instances
PD M M M 3 1
In the above eg, I have taken leaves on Fri, Mon and Tue, with Sat/Sun being weekends and non working days. Therefore total number of leaves taken = 3 and number of instances of leaves = 1.
I have used the below formula to calculate the instances of leaves :
=SUMPRODUCT(--ISNUMBER(MATCH($A1:$F1,{"M","MH"},0)),--ISNA(MATCH($B1:$G1,{"M","MH"},0)))
However, as the above does not discount the weekends, the result for instances = 2.
I need assistance to arrive at a formula that will not count the weekends. Some departments have Fri and Sat as weekends and therefore it needs to be flexible enough for us to amend the days that need to be discounted.
Any assistance that can be provided is appreciated as always.
Thank you again.