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

D

dlmoore99

Board Regular
Joined
May 20, 2011
Messages
88
Office Version
  1. 2019
Platform
  1. MacOS
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/23What it is doingSunWhat it should look like
11/06/23C-09 C-11 (C-07)C-09 C-11 (C-07)MonC-09 C-11 (C-07)
11/07/23C-01 C-10 (C-26)C-01 C-10 (C-26)TueC-01 C-10 (C-26)
11/08/23C-06 C-12C-06 C-12WedC-06 C-12
11/09/23C-05 C-13C-05 C-13 **C-02 C-04 Holiday**ThuC-05 C-13 **C-02 C-04 Holiday**
11/10/23C-02 C-04C-02 C-04 **C-02 C-04 Holiday**FriC-02 C-04
11/11/23C-02 C-04HolidaySatHoliday
11/12/23Sun
11/13/23T6T6MonT6
11/14/23C-09 C-11 (C-07)C-09 C-11 (C-07)TueC-09 C-11 (C-07)
11/15/23C-01 C-10 (C-26)C-01 C-10 (C-26)WedC-01 C-10 (C-26)
11/16/23C-06 C-12C-06 C-12ThuC-06 C-12
11/17/23C-05 C-13C-05 C-13FriC-05 C-13
11/18/23C-05 C-13C-05 C-13SatC-05 C-13
11/19/23Sun
11/20/23C-02 C-04C-02 C-04MonC-02 C-04
11/21/23T6T6 **C-01 C-10 (C-26) Holiday**TueT6
11/22/23C-09 C-11 (C-07)C-09 C-11 (C-07) **C-01 C-10 (C-26) Holiday**WedC-09 C-11 (C-07) **C-01 C-10 (C-26) Holiday**
11/23/23C-01 C-10 (C-26)HolidayThuHoliday
11/24/23C-06 C-12C-06 C-12FriC-06 C-12
11/25/23C-06 C-12C-06 C-12SatC-06 C-12
11/26/23Sun
11/27/23C-05 C-13C-05 C-13MonC-05 C-13
11/28/23C-02 C-04C-02 C-04TueC-02 C-04
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
Please copy and paste XL2BB as mini sheet. It helps to understand the existing formulae and errors better.
 
Upvote 0
SanjayGMusafir said:
Please copy and paste XL2BB as mini sheet. It helps to understand the existing formulae and errors better.
Click to expand...
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))))))))))))))
 
Upvote 0
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.

Excel Formula: 
=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)
 
Upvote 0
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)
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

L
  • Solved
Help Please
2 3
Replies
21
Views
405
Asbestos_Jen
Asbestos_Jen
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
235
dshtesy
D
S
  • Solved
Count of active employees between dates.
Replies
3
Views
620
Fluff
Fluff
S
  • Question Question
Two way look up with mutiple duplicate criteria with mutiple return
Replies
3
Views
170
sanrama
S
R
  • Solved
Age Group Ranging by gender
Replies
2
Views
228
Raj1979
R

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top