nested IF returns "false" instead of if false

K

konkrash

New Member
Joined
Aug 11, 2016
Messages
10
hi guys, kinda new to xl and i have this problem with a somewhat complicated nested IF.

the formula:
=IF(A120=IFNA(VLOOKUP(A120,$R$7:$R$57,1,FALSE),A120+1),
IF(AND(WEEKDAY(A118)=7,WEEKDAY(A119)=7,WEEKDAY(A120)=1),A120,
IF(AND(WEEKDAY(A118)=7,WEEKDAY(A119)=1,WEEKDAY(A120)=1),A120,
IF(AND(WEEKDAY(A118)=1,WEEKDAY(A119)=1,WEEKDAY(A120)=1),A120+1,


IF(AND(WEEKDAY(A118)=1,WEEKDAY(A119)=1,WEEKDAY(A120)=2),A120,
IF(AND(WEEKDAY(A118)=1,WEEKDAY(A119)=2,WEEKDAY(A120)=2),A120,
IF(AND(WEEKDAY(A118)=2,WEEKDAY(A119)=2,WEEKDAY(A120)=2),A120+1,


IF(AND(WEEKDAY(A118)=2,WEEKDAY(A119)=2,WEEKDAY(A120)=3),A120,
IF(AND(WEEKDAY(A118)=2,WEEKDAY(A119)=3,WEEKDAY(A120)=3),A120,
IF(AND(WEEKDAY(A118)=3,WEEKDAY(A119)=3,WEEKDAY(A120)=3),A120+1,


IF(AND(WEEKDAY(A118)=3,WEEKDAY(A119)=3,WEEKDAY(A120)=4),A120,
IF(AND(WEEKDAY(A118)=3,WEEKDAY(A119)=4,WEEKDAY(A120)=4),A120,
IF(AND(WEEKDAY(A118)=4,WEEKDAY(A119)=4,WEEKDAY(A120)=4),A120+1,


IF(AND(WEEKDAY(A118)=4,WEEKDAY(A119)=4,WEEKDAY(A120)=5),A120,
IF(AND(WEEKDAY(A118)=4,WEEKDAY(A119)=5,WEEKDAY(A120)=5),A120,
IF(AND(WEEKDAY(A118)=5,WEEKDAY(A119)=5,WEEKDAY(A120)=5),A120+1,


IF(AND(WEEKDAY(A119)=7,WEEKDAY(A120)=1),A120,
IF(AND(WEEKDAY(A119)=1,WEEKDAY(A120)=1),A120+1,
IF(AND(WEEKDAY(A119)=1,WEEKDAY(A120)=2),A120,
IF(AND(WEEKDAY(A119)=2,WEEKDAY(A120)=2),A120+1,
IF(AND(WEEKDAY(A119)=2,WEEKDAY(A120)=3),A120,
IF(AND(WEEKDAY(A119)=3,WEEKDAY(A120)=3),A120+1,
IF(AND(WEEKDAY(A119)=3,WEEKDAY(A120)=4),A120,
IF(AND(WEEKDAY(A119)=4,WEEKDAY(A120)=4),A120+1,
IF(AND(WEEKDAY(A119)=4,WEEKDAY(A120)=5),A120,
IF(AND(WEEKDAY(A119)=5,WEEKDAY(A120)=5),A120+1,
IF(AND(WEEKDAY(A119)=5,WEEKDAY(A120)=6),A120,
IF(AND(WEEKDAY(A118)=5,WEEKDAY(A119)=6,WEEKDAY(A120)=6),A120,IF(AND(WEEKDAY(A118)=6,WEEKDAY(A119)=6,WEEKDAY(A120)=6),A120+1,
IF(AND(WEEKDAY(A118)=6,WEEKDAY(A119)=7,WEEKDAY(A120)=7),A120,IF(AND(WEEKDAY(A118)=6,WEEKDAY(A119)=6,WEEKDAY(A120)=7),A120,
IF(AND(WEEKDAY(A118)=7,WEEKDAY(A119)=7,WEEKDAY(A120)=7),A120+1,"?"))))))))))))))))))))))))))))))))


so, what im trying to do here is;
in column A i have a date, and for each day i have 2 or 3 rows depending on a few parameters.
i need to check if:
(a) this date is a work day, i.e. sunday through thuesday.
(b) if its a weekend, i.e. friday or saturday.
(c) if its a holiday, which is in column R.

if its a normal workday, sun - thu, than there should be only 2 rows with the same date, but if its either a weekend or a holiday than there should be 3 rows.

the formula works great if is a holiday, but if its not it just returns "FALSE" instead of the false argument of the formula..
ive cracked my head open on this and still havent figured out whats went wrong here.

any help would be greatly appreciated :)
 
Sort by date Sort by votes
by the way, the very last false argument in the formula, "?", is meant so as to point out that none of the above argument were met.
 
Upvote 0

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
heres a sample:
01/10/2016
holidays
02/10/2016
01/10/201603/10/2016
01/10/201604/10/2016
01/10/201611/10/2016
02/10/201612/10/2016
FALSE16/10/2016
FALSE17/10/2016
FALSE23/10/2016
01/01/190024/10/2016
FALSE25/12/2016
FALSE26/12/2016
FALSE27/12/2016
01/01/190028/12/2016
FALSE29/12/2016
FALSE01/01/2017
FALSE10/04/2017
01/01/190011/04/2017
FALSE12/04/2017
FALSE13/04/2017
FALSE16/04/2017
01/01/190017/04/2017
FALSE18/04/2017
FALSE02/05/2017
FALSE30/05/2017
01/01/190031/05/2017
FALSE01/06/2017
FALSE20/09/2017
FALSE21/09/2017
01/01/1900
FALSE
FALSE
FALSE
01/01/1900
FALSE
FALSE
FALSE
01/01/1900
FALSE
FALSE
FALSE
01/01/1900
FALSE
FALSE
FALSE
01/01/1900
FALSE
FALSE
FALSE
01/01/1900
FALSE
FALSE
FALSE
01/01/1900
FALSE
FALSE
FALSE
01/01/1900
FALSE
FALSE
FALSE
01/01/1900
FALSE
FALSE
FALSE
01/01/1900
FALSE
FALSE
FALSE
01/01/1900
FALSE
FALSE
FALSE
01/01/1900
FALSE
FALSE
FALSE
01/01/1900
FALSE
FALSE
FALSE
01/01/1900

<colgroup><col><col span="2"><col></colgroup><tbody>
</tbody>
 
Upvote 0
Is this what you want?

ABCDEQR
1Datesholidays
21/10/2016Start date (Sunday)2/10/2016
31/10/2016repeated3/10/2016
41/11/2016Next day4/10/2016
51/11/201611/10/2016
61/12/201612/10/2016
71/12/201616/10/2016
81/13/201617/10/2016
91/13/201623/10/2016
101/14/201624/10/2016
111/14/201625/12/2016
121/15/201626/12/2016
131/15/201627/12/2016
141/15/201628/12/2016
151/16/201629/12/2016
161/16/20161/1/2017
171/16/201610/4/2017
181/17/201611/4/2017
191/17/201612/4/2017
201/18/201613/04/2017
211/18/201616/04/2017
221/19/201617/04/2017
231/19/201618/04/2017
241/20/20162/5/2017
251/20/201630/05/2017
261/21/201631/05/2017
271/21/20161/6/2017
281/22/201620/09/2017
291/22/201621/09/2017

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet7​

Worksheet Formulas
CellFormula
A5=IF(A3<>A4,A4,IF(OR(WEEKDAY(A4)>=6,ISNUMBER(MATCH(A4,R$2:R$29,0))),IF(A4=A2,A4+1,A4),A4+1))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>



Put the first 3 dates in column A. Put the formula in A5 and copy down.
 
Upvote 0
hmmm, not quite right.
this only return 2 rows per day regardless if its a weekend or a holidy, which should be 3 rows
 
Upvote 0
Check 1/15/2016 and 1/16/2016 on the example sheet. 3 rows each. I didn't extend the sample as far as your first holiday (2/10/2016), but I tested it for 6 months or so, and the holidays also showed up with 3 rows each.
 
Upvote 0
my bad, i didnt changed the formula to match the correct range in my file, so it messed up the results.
it works great not, thanks a lot!
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
IF statement
Replies
1
Views
585
KillerOfGiants
K
L
  • Question
How to Repeat shifts and have it auto fill after giving first line.
Replies
2
Views
126
LPHBELLO
L
R
  • Solved
Combination Sumif and/or Countif on a condition
Replies
3
Views
175
Fluff
Fluff
SaraWitch
  • Solved
VLOOKUP formula with multiple IF statements
Replies
2
Views
531
SaraWitch
SaraWitch
K
  • Question
Dynamic Address for Use in Function Always returns ERROR
Replies
3
Views
302
Eric W
Eric W

Forum statistics

Threads
1,216,100
Messages
6,128,824
Members
449,470
Latest member
Subhash Chand

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