Count number of instances discounting weekends

[PD]

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.

 

[Domenic]

Does the data contain true date values? If not, use true date values. So, for example, assuming that A1:G3 contains the following data...

<TABLE style="WIDTH: 344pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=459><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 48pt" span=5 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=64>Date</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=75>12/11/09</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64>12/12/09</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64>12/13/09</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64>12/14/09</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64>12/15/09</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64>12/16/09</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Day</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>Fri</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>Sat</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>Sun</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>Mon</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>Tue</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>Wed</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>PD</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>M</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>M</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>M</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD></TR></TBODY></TABLE>

...try the following formula...

H3, confirmed with CONTROL+SHIFT+ENTER, and copied down (assuming you're keeping track of attendance for others):

=SUM(IF(FREQUENCY(IF(ISNUMBER(MATCH(B3:G3,{"M","MH"},0))+ISNUMBER(MATCH(IF(ISNUMBER(MATCH($B$2:$G$2,{"Sat","Sun"},0)),$B$1:$G$1,"#"),IF(ISNUMBER(MATCH(B3:G3,{"M","MH"},0)),IF($B$2:$G$2="Fri",$B$1:$G$1+1,IF($B$2:$G$2="Mon",$B$1:$G$1-1))),0)),COLUMN(B3:G3)),IF(ISNA(MATCH(B3:G3,{"M","MH"},0))*ISNA(MATCH($B$2:$G$2,{"Sat","Sun"},0)),COLUMN(B3:G3))),1))

Adjust the ranges, accordingly.

 

[PD]

Thanks Domenic.


I tried using the below. It however shows an error against the argument marked in BOLD. I entered as cntrl+shift+enter...

Also, I have custom formated the date for reflect just the day, ie, 11/01/2010 would show only 11 (format d instead od dd/mm/yyyy). Would this be an issue. I would prefer to use the custom format as it allows me to show considerable data on a single view, ie, I would rather not increase the column width while keeping the data easily viewable.

=SUM(IF(FREQUENCY(IF(ISNUMBER(MATCH(B3:G3,{"M","MH"},0))+ISNUMBER(MATCH(IF(ISNUMBER(MATCH($B$2:$G$2,{"Sat","Sun"},0)),$B$1:$G$1,"#"),IF(ISNUMBER(MATCH(B3:G3,{"M","MH"},0)),IF($B$2:$G$2="Fri",$B$1:$G$1+1,IF($B$2:$G$2="Mon",$B$1:$G$1-1))),0)),COLUMN(B3:G3)),IF(ISNA(MATCH(B3:G3,{"M","MH"},0))*ISNA(MATCH($B$2:$G$2,{"Sat","Sun"},0)),COLUMN(B3:G3))),1))

Thank you again for your help.

Regards,

 

[Domenic]

It looks like the limit for nested functions has been exceeded for your version of Excel. Try the following instead...

1) Define the following...

Select H3

Insert > Name > Define

Name: Array1

Refers to:

=IF(ISNUMBER(MATCH($B$2:$G$2,{"Sat","Sun"},0)),$B$1:$G$1,"#")

Click Add

Name: Array2

Refers to:

=IF(ISNUMBER(MATCH($B3:$G3,{"M","MH"},0)),IF($B$2:$G$2="Fri",$B$1:$G$1+1,IF($B$2:$G$2="Mon",$B$1:$G$1-1)))

Click Ok

2) Then try...

H3, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=SUM(IF(FREQUENCY(IF(ISNUMBER(MATCH($B3:$G3,{"M","MH"},0))+ISNUMBER(MATCH(Array1,Array2,0)),COLUMN($B3:$G3)),IF(ISNA(MATCH($B3:$G3,{"M","MH"},0))*ISNA(MATCH($B$2:$G$2,{"Sat","Sun"},0)),COLUMN($B3:$G3))),1))

 

[PD]

Brilliant.. this works fantastic.

Thanks a lot Domenic.

Just the one other thing..the weekends differ for some of my processes, ie, some have weekly offs on sat+sun, some on fri+sat...can the below formula be referred to a cell (eg- I3, I4 in the below eg), ie, a table where we list the days that need to be considered as weekends.

Also, will it work if the -1 and +1 days (Fri and Mon in the below eg) are also referred from specific cells.

Thanks again.

 

[Domenic]

Let's assume that J1:L3 contains the following table, which lists each employee and their corresponding weekly-off...

<TABLE style="WIDTH: 147pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=196><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20 width=64>PD</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=64>JS</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 51pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=68>JD</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Sat</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>Fri</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>Sat</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Sun</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>Sat</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>Sun</TD></TR></TBODY></TABLE>

First, replace...

{"Sat","Sun"}

with

=INDEX($J$2:$L$3,0,MATCH($A3,$J$1:$L$1,0))

Then, define the following...

Select H3

Insert > Name > Define

Name: DayBefore

Refers to:

=CHOOSE(MATCH(INDEX($J$2:$L$3,1,MATCH($A3,$J$1:$L$1,0)),{"Mon","Tue","Wed","Thu","Fri","Sat","Sun"},0),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")

Click Add

Name: DayAfter

Refers to:

=CHOOSE(MATCH(INDEX($J$2:$L$3,2,MATCH($A3,$J$1:$L$1,0)),{"Mon","Tue","Wed","Thu","Fri","Sat","Sun"},0),"Tue","Wed","Thu","Fri","Sat","Sun","Mon")

Click Ok

Lastly, replace..

="Fri"

with

=DayBefore

and

="Mon"

with

=DayAfter