IF, IF(And(countif)) formula not working correctly

[dlmoore99]

I will be checking 3 dates along with day of week for desired results in colunm C (C1075). First, I need to check cell if date ( A1075) is a Sunday, if yes then return blank (that part is working correctly). then check same date (A1075) to see if it is a holiday from a named list on a different sheet, if yes then return "Holiday" (that part is working correctly). Then need to check if date in cell (A1076) if it is a holiday and depending on day of week, if yes then concat two cells B1075 and B1076, if false check next date (A1077) if it is a holiday and depending on day of week, if yes then concat two cells B1075 and B1077. If holiday is a Monday then concat data goes to previous Saturday; if holiday is a Saturday then concat results go to previous Thursday; if holiday is on Sunday then concat data goes to following Monday; if holiday is on any other day of week then concat data goes to previous day. would like to shrink formulas well, I hope that I've explained it well enough for you to understand. Thanks for your help.

11/05/23		What it is doing	Sun	What it should look like
11/06/23	C-09 C-11 (C-07)	C-09 C-11 (C-07)	Mon	C-09 C-11 (C-07)
11/07/23	C-01 C-10 (C-26)	C-01 C-10 (C-26)	Tue	C-01 C-10 (C-26)
11/08/23	C-06 C-12	C-06 C-12	Wed	C-06 C-12
11/09/23	C-05 C-13	C-05 C-13 **C-02 C-04 Holiday**	Thu	C-05 C-13 **C-02 C-04 Holiday**
11/10/23	C-02 C-04	C-02 C-04 **C-02 C-04 Holiday**	Fri	C-02 C-04
11/11/23	C-02 C-04	Holiday	Sat	Holiday
11/12/23			Sun
11/13/23	T6	T6	Mon	T6
11/14/23	C-09 C-11 (C-07)	C-09 C-11 (C-07)	Tue	C-09 C-11 (C-07)
11/15/23	C-01 C-10 (C-26)	C-01 C-10 (C-26)	Wed	C-01 C-10 (C-26)
11/16/23	C-06 C-12	C-06 C-12	Thu	C-06 C-12
11/17/23	C-05 C-13	C-05 C-13	Fri	C-05 C-13
11/18/23	C-05 C-13	C-05 C-13	Sat	C-05 C-13
11/19/23			Sun
11/20/23	C-02 C-04	C-02 C-04	Mon	C-02 C-04
11/21/23	T6	T6 **C-01 C-10 (C-26) Holiday**	Tue	T6
11/22/23	C-09 C-11 (C-07)	C-09 C-11 (C-07) **C-01 C-10 (C-26) Holiday**	Wed	C-09 C-11 (C-07) **C-01 C-10 (C-26) Holiday**
11/23/23	C-01 C-10 (C-26)	Holiday	Thu	Holiday
11/24/23	C-06 C-12	C-06 C-12	Fri	C-06 C-12
11/25/23	C-06 C-12	C-06 C-12	Sat	C-06 C-12
11/26/23			Sun
11/27/23	C-05 C-13	C-05 C-13	Mon	C-05 C-13
11/28/23	C-02 C-04	C-02 C-04	Tue	C-02 C-04

 

[SanjayGMusafir]

Please copy and paste XL2BB as mini sheet. It helps to understand the existing formulae and errors better.

 

[dlmoore99]

Please copy and paste XL2BB as mini sheet. It helps to understand the existing formulae and errors better.

Sorry, but I'm getting the spinning wheel of death and excel freezes when I to try to do the mini sheet, here is the formula in C1075:

=IF(D1075="Sun","",IF(COUNTIF(US_Holidays,A1075),"Holiday",IF(AND(COUNTIF(US_Holidays,A1076),D1076="Mon"),CONCAT(SUBSTITUTE(B1075,CHAR(10)," ")&CHAR(10)&"**"&SUBSTITUTE(B1076,CHAR(10)," ")," Holiday**"),IF(AND(COUNTIF(US_Holidays,A1076),D1076="Tue"),CONCAT(SUBSTITUTE(B1075,CHAR(10)," ")&CHAR(10)&"**"&SUBSTITUTE(B1076,CHAR(10)," ")," Holiday**"),IF(AND(COUNTIF(US_Holidays,A1076),D1076="Wed"),CONCAT(SUBSTITUTE(B1075,CHAR(10)," ")&CHAR(10)&"**"&SUBSTITUTE(B1076,CHAR(10)," ")," Holiday**"),IF(AND(COUNTIF(US_Holidays,A1076),D1076="Thu"),CONCAT(SUBSTITUTE(B1075,CHAR(10)," ")&CHAR(10)&"**"&SUBSTITUTE(B1076,CHAR(10)," ")," Holiday**"),IF(AND(COUNTIF(US_Holidays,A1076),D1076="Fri"),CONCAT(SUBSTITUTE(B1075,CHAR(10)," ")&CHAR(10)&"**"&SUBSTITUTE(B1076,CHAR(10)," ")," Holiday**"),IF(AND(COUNTIF(US_Holidays,A1076),D1076="Sat"),CONCAT(SUBSTITUTE(B1075,CHAR(10)," ")&CHAR(10)&"**"&SUBSTITUTE(B1076,CHAR(10)," ")," Holiday**"),IF(AND(COUNTIF(US_Holidays,A1077),D1077="Mon"),CONCAT(SUBSTITUTE(B1075,CHAR(10)," ")&CHAR(10)&"**"&SUBSTITUTE(B1077,CHAR(10)," ")," Holiday**"),IF(AND(COUNTIF(US_Holidays,A1077),D1077="Tue"),CONCAT(SUBSTITUTE(B1075,CHAR(10)," ")&CHAR(10)&"**"&SUBSTITUTE(B1077,CHAR(10)," ")," Holiday**"),IF(AND(COUNTIF(US_Holidays,A1077),D1077="Wed"),CONCAT(SUBSTITUTE(B1075,CHAR(10)," ")&CHAR(10)&"**"&SUBSTITUTE(B1077,CHAR(10)," ")," Holiday**"),IF(AND(COUNTIF(US_Holidays,A1077),D1077="Thu"),CONCAT(SUBSTITUTE(B1075,CHAR(10)," ")&CHAR(10)&"**"&SUBSTITUTE(B1077,CHAR(10)," ")," Holiday**"),IF(AND(COUNTIF(US_Holidays,A1077),D1077="Fri"),CONCAT(SUBSTITUTE(B1075,CHAR(10)," ")&CHAR(10)&"**"&SUBSTITUTE(B1077,CHAR(10)," ")," Holiday**"),IF(AND(COUNTIF(US_Holidays,A1077),D1077="Sat"),CONCAT(SUBSTITUTE(B1075,CHAR(10)," ")&CHAR(10)&"**"&SUBSTITUTE(B1077,CHAR(10)," ")," Holiday**"),B1075))))))))))))))

 

[SanjayGMusafir]

There are two situations -

When I'm looking through your data and information you provided 10Nov2023 should be what excel is doing but your data says it should do something different... Unable to understand that tried much but was unable to decode that.

As of shortening the current formula you can try as below. It should work exactly the way it's working now. I can modify it once I understand what different you want it to do.

Remember, Excel will behave in the order of sequence and shall stop at first thing that it finds to be true. So explain me or excel things in the order of your preference.

=IFS(D1075="Sun","",
COUNTIF(US_Holidays,A1075),"Holiday",
AND(COUNTIF(US_Holidays,A1076),D1076<>"Sun"),CONCAT(SUBSTITUTE(B1075,CHAR(10)," ")&CHAR(10)&"**"&SUBSTITUTE(B1076,CHAR(10)," ")," Holiday**"),
AND(COUNTIF(US_Holidays,A1077),D1077<>"Sun"),CONCAT(SUBSTITUTE(B1075,CHAR(10)," ")&CHAR(10)&"**"&SUBSTITUTE(B1077,CHAR(10)," ")," Holiday**"),
True,B1075)

 

[dlmoore99]

Sanjay, based off day of the week, I needed to have the days off in column B to move to other days of the week, I was able to figure it out and here is the results, thank you for your help

=IFS(D1075="Sun","",COUNTIF(US_Holidays,A1075),"Holiday",
AND(COUNTIF(US_Holidays,A1076),D1076="Sun"),CONCAT(SUBSTITUTE(B1075,CHAR(10)," ")&CHAR(10)&"**"&SUBSTITUTE(B1076,CHAR(10)," ")," Holiday**"),
AND(COUNTIF(US_Holidays,A1076),D1076="Mon"),CONCAT(SUBSTITUTE(B1075,CHAR(10)," ")&CHAR(10)&"**"&SUBSTITUTE(B1076,CHAR(10)," ")," Holiday**"),
AND(COUNTIF(US_Holidays,A1076),D1076="Tue"),CONCAT(SUBSTITUTE(B1075,CHAR(10)," ")&CHAR(10)&"**"&SUBSTITUTE(B1076,CHAR(10)," ")," Holiday**"),
AND(COUNTIF(US_Holidays,A1076),D1076="Wed"),CONCAT(SUBSTITUTE(B1075,CHAR(10)," ")&CHAR(10)&"**"&SUBSTITUTE(B1076,CHAR(10)," ")," Holiday**"),
AND(COUNTIF(US_Holidays,A1076),D1076="Thu"),CONCAT(SUBSTITUTE(B1075,CHAR(10)," ")&CHAR(10)&"**"&SUBSTITUTE(B1076,CHAR(10)," ")," Holiday**"),
AND(COUNTIF(US_Holidays,A1076),D1076="Fri"),CONCAT(SUBSTITUTE(B1075,CHAR(10)," ")&CHAR(10)&"**"&SUBSTITUTE(B1076,CHAR(10)," ")," Holiday**"),
AND(COUNTIF(US_Holidays,A1076),D1076="Sat"),B1075,
AND(COUNTIF(US_Holidays,A1077),D1077="Sun"),CONCAT(SUBSTITUTE(B1075,CHAR(10)," ")&CHAR(10)&"**"&SUBSTITUTE(B1077,CHAR(10)," ")," Holiday**"),
AND(COUNTIF(US_Holidays,A1077),D1077="Mon"),CONCAT(SUBSTITUTE(B1075,CHAR(10)," ")&CHAR(10)&"**"&SUBSTITUTE(B1077,CHAR(10)," ")," Holiday**"),
AND(COUNTIF(US_Holidays,A1077),D1077="Tue"),B1075,
AND(COUNTIF(US_Holidays,A1077),D1077="Wed"),B1075,
AND(COUNTIF(US_Holidays,A1077),D1077="Thu"),B1075,
AND(COUNTIF(US_Holidays,A1077),D1077="Fri"),B1075,
AND(COUNTIF(US_Holidays,A1077),D1077="Sat"),CONCAT(SUBSTITUTE(B1075,CHAR(10)," ")&CHAR(10)&"**"&SUBSTITUTE(B1077,CHAR(10)," ")," Holiday**"),
TRUE,B1075)

 

[SanjayGMusafir]

Glad you could find a solution to your problem.