Holidays date range based on multiple criterias

[dellzy]

Hi,

I'm trying to have a formula to return the 5th last date of the month after deducting the off days/PH & closing date which we list in a table like below:-

[TABLE="width: 238"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]*Event/PH[/TD]
[TD]Dept[/TD]
[/TR]
[TR]
[TD]1/1/2014[/TD]
[TD]New Year[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4/1/2014[/TD]
[TD]Off-Day[/TD]
[TD]CLM[/TD]
[/TR]
[TR]
[TD]5/1/2014[/TD]
[TD]Off-Day[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]7/1/2014[/TD]
[TD]Off-Day[/TD]
[TD]CLM[/TD]
[/TR]
[TR]
[TD]8/1/2014[/TD]
[TD]Off-Day[/TD]
[TD]POS[/TD]
[/TR]
[TR]
[TD]11/1/2014[/TD]
[TD]School Holidays[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]12/1/2014[/TD]
[TD]Off-Day[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]13/1/2014[/TD]
[TD]Off-Day[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]18/1/2014[/TD]
[TD]Off-Day[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]19/1/2014[/TD]
[TD]Off-Day[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]23/1/2014[/TD]
[TD]Off-Day[/TD]
[TD]POS[/TD]
[/TR]
[TR]
[TD]24/1/2014[/TD]
[TD]Thaipusam[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]26/1/2014[/TD]
[TD]Off-Day[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]28/1/2014[/TD]
[TD]Closing[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]29/1/2014[/TD]
[TD]Off-Day[/TD]
[TD]CLM[/TD]
[/TR]
[TR]
[TD]31/1/2014[/TD]
[TD]CNY[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2/2/2014[/TD]
[TD]Off-Day[/TD]
[TD]POS[/TD]
[/TR]
[TR]
[TD]5/2/2014[/TD]
[TD]Off-Day[/TD]
[TD]POS[/TD]
[/TR]
[TR]
[TD]20/2/2014[/TD]
[TD]Off-Day[/TD]
[TD]POS[/TD]
[/TR]
[TR]
[TD]21/2/2014[/TD]
[TD]Off-Day[/TD]
[TD]POS[/TD]
[/TR]
[TR]
[TD]23/2/2014[/TD]
[TD]Off-Day[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]28/2/2014[/TD]
[TD]Off-Day[/TD]
[TD]OS[/TD]
[/TR]
[TR]
[TD]3/3/2014[/TD]
[TD]Whateva[/TD]
[TD]NB[/TD]
[/TR]
[TR]
[TD]3/3/2014[/TD]
[TD]Off-Day[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]6/3/2014[/TD]
[TD]Whateva[/TD]
[TD]NB[/TD]
[/TR]
[TR]
[TD]6/3/2014[/TD]
[TD]AidilAdha[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]9/3/2014[/TD]
[TD]Whateva[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]12/3/2014[/TD]
[TD]Whateva[/TD]
[TD]POS[/TD]
[/TR]
[TR]
[TD]20/3/2014[/TD]
[TD]Whateva[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]22/3/2014[/TD]
[TD]Hari Raya[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]25/3/2014[/TD]
[TD]Off-Day[/TD]
[TD]POS[/TD]
[/TR]
[TR]
[TD]28/6/2014[/TD]
[TD]Closing[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]26/11/2014[/TD]
[TD]Closing[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

Basically, we will use formula like below to achieve this :-

{=WORKDAY.INTL(DATE($C$1,MONTH(DATEVALUE($T$2&" 1"))+1,1),-5,"0000000",$A$11:$A$1098,0))}

cell C1=Year
cell T2=Month
Col A = Dates

However, my situation right now is that the range $A$11:$A$1098 would need to be based on criteria in col C ==> Dept name or blank. This is to ensure the 5th last working date for the particular staff of the respective dept will not be compromised by deducting those dates which are not the dept off-days or closing date. This is due to the fact that our company has non-fixed off-days and production closing date for each dept. This 5th last working date is significant for our monthly incentive calculation. Those blanks one shud indicate that the holiday is for all depts. Hence, my holiday dates range for the particular dept must include both values.

I tried several formulas like below:-

=WORKDAY.INTL(DATE($C$1,MONTH(DATEVALUE($T$2&" 1"))+1,1),-5,"0000000",IF("POS"=$C$11:$C$1098,$A$11:$A$1098,0))
Result : Works BUT it doesn't count for Dept value = blank
=WORKDAY.INTL(DATE($C$1,MONTH(DATEVALUE($T$2&" 1"))+1,1),-5,"0000000",IF(" "=$C$11:$C$1098,$A$11:$A$1098,0)) << with space
Result : Works BUT it doesn't count for Dept value = POS or NB or CLM and weird to have the result as I didn't put any space for the blank dept name


=WORKDAY.INTL(DATE($C$1,MONTH(DATEVALUE($T$2&" 1"))+1,1),-5,"0000000",IF(OR("POS"=$D$11:$D$1098,""=$D$11:$D$1098),$A$11:$A$1098,0))
=WORKDAY.INTL(DATE($C$1,MONTH(DATEVALUE($T$2&" 1"))+1,1),-5,"0000000",IF(MATCH(1,("POS"=$D$11:$D$1098)+(""=$D$11:$D$1098),0),$A$11:$A$1098,0))
=WORKDAY.INTL(DATE($C$1,MONTH(DATEVALUE($T$2&" 1"))+1,1),-5,"0000000",IF(""=$C$11:$C$1098,$A$11:$A$1098,0)) << without space
All above returned error #VALUE!

FYI, the value from each cell in the table above is referenced from another worksheet.

Hopefully, anyone can help me on this as I have tried over 2 weeks but couldn't find solution.

 

[FormR]

Hi, welcome to the forum:

If I have understood correctly, give this array formula (which needs to be confirmed with CTRL+SHIFT+ENTER) a try:

=WORKDAY.INTL(EOMONTH("1"&T1&C1,0),-5,"0000000",IF((C11:C1098="POS")+(C11:C1098=""),A11:A1098,0))

EDIT:

And maybe try this Non-Array alternative:

=WORKDAY.INTL(EOMONTH("1"&T1&C1,0),-5,"0000000",INDEX(((C11:C1098="POS")+(C11:C1098=""))*A11:A1098,0))

 

[dellzy]

Hi FormR,

First of all, I really thank you for your time and effort to provide me the solution. I have tried both but they didn't give me the correct values. However, your solution on holidays range have helped to trigger my other solution as below:-

=WORKDAY.INTL(DATE($C$1,MONTH(DATEVALUE($T$2&" 1"))+1,1),-6,"0000000",IF(ISNUMBER(IF((($C$11:$C$1098="POS")+($C$11:$C$1098=""))*($B$11:$B$1098<>"Closing"),$A$11:$A$1098,0)),IF((($C$11:$C$1098="POS")+($C$11:$C$1098=""))*($B$2:$B$28<>"Closing"),$A$11:$A$1098,0),0))

Changes made to the number of days deduction to 6 instead of 5 so that it will count in the date exactly 5th last date, not before the 5th last date. Then, after "filtering" the date range , which is supposed to count only "POS" or "" and not "Closing", it must checked if the dates are number or not,then it'll return the range accordingly. Actually, by normal logic thinking, if the formula checks those considered non-numeric,it will be ignored, but I notice when I use this ISNUMBER formula, it will somehow read all as dates as I required.

Thanks again for the assistance.

 

[FormR]

Thanks again for the assistance.

You're welcome!

=WORKDAY.INTL(DATE($C$1,MONTH(DATEVALUE($T$2&" 1"))+1,1),-6,"0000000",IF(ISNUMBER(IF((($C$11:$C$1098="POS")+($C$11:$C$1098=""))*($B$11:$B$1098<>"Closing"),$A$11:$A$1098,0)),IF((($C$11:$C$1098="POS")+($C$11:$C$1098=""))*($B$2:$B$28<>"Closing"),$A$11:$A$1098,0),0))

I wonder if the red part is a mistake in your post?

If so, I think this gives the same results..

ARRAY entered..

=WORKDAY.INTL(EOMONTH("1"&T2&C1,0)+1,-6,"0000000",IF((C11:C1098="POS")+(C11:C1098=""),IF(B11:B1098<>"Closing",IF(ISNUMBER(A11:A1098),A11:A1098,0),0),0))

 

[dellzy]

Hi FormR,

Very sorry for the delays as I was really cramp with work and travel.Really appreciate your input and yes, my range was wrong as I was testing on some other worksheet and take shorter samples only. and your alternative formula on getting the end of month date also works. Please advise on how to close this thread as solved as I normally been advised when I posted at excelforum.com

 

[FormR]

Please advise on how to close this thread as solved as I normally been advised when I posted at excelforum.com

Hi, glad to have helped.

Threads stay open on this forum, in part to encouraged others to contribute even after you have a working solution (you can often end up with better/shorter/more efficient answers that way)

 

[dellzy]

Oh, that's great!

Tqvm!