nested IF returns "false" instead of if false

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 :)
 
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

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
heres a sample:
[TABLE="width: 286"]
<colgroup><col><col span="2"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/10/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]holidays[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]02/10/2016[/TD]
[/TR]
[TR]
[TD]01/10/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD]03/10/2016[/TD]
[/TR]
[TR]
[TD]01/10/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD]04/10/2016[/TD]
[/TR]
[TR]
[TD]01/10/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD]11/10/2016[/TD]
[/TR]
[TR]
[TD]02/10/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD]12/10/2016[/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD]16/10/2016[/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD]17/10/2016[/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD]23/10/2016[/TD]
[/TR]
[TR]
[TD]01/01/1900[/TD]
[TD][/TD]
[TD][/TD]
[TD]24/10/2016[/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD]25/12/2016[/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD]26/12/2016[/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD]27/12/2016[/TD]
[/TR]
[TR]
[TD]01/01/1900[/TD]
[TD][/TD]
[TD][/TD]
[TD]28/12/2016[/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD]29/12/2016[/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD]01/01/2017[/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD]10/04/2017[/TD]
[/TR]
[TR]
[TD]01/01/1900[/TD]
[TD][/TD]
[TD][/TD]
[TD]11/04/2017[/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD]12/04/2017[/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD]13/04/2017[/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD]16/04/2017[/TD]
[/TR]
[TR]
[TD]01/01/1900[/TD]
[TD][/TD]
[TD][/TD]
[TD]17/04/2017[/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD]18/04/2017[/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD]02/05/2017[/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD]30/05/2017[/TD]
[/TR]
[TR]
[TD]01/01/1900[/TD]
[TD][/TD]
[TD][/TD]
[TD]31/05/2017[/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD]01/06/2017[/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD]20/09/2017[/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD]21/09/2017[/TD]
[/TR]
[TR]
[TD]01/01/1900[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01/01/1900[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01/01/1900[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01/01/1900[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01/01/1900[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01/01/1900[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01/01/1900[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01/01/1900[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01/01/1900[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01/01/1900[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01/01/1900[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01/01/1900[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01/01/1900[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01/01/1900[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Is this what you want?

ABCDEQR

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]Dates[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]holidays[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/10/2016[/TD]
[TD="bgcolor: #FAFAFA"]Start date (Sunday)[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]2/10/2016[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/10/2016[/TD]
[TD="bgcolor: #FAFAFA"]repeated[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]3/10/2016[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/11/2016[/TD]
[TD="bgcolor: #FAFAFA"]Next day[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]4/10/2016[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/11/2016[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]11/10/2016[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/12/2016[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]12/10/2016[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/12/2016[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]16/10/2016[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/13/2016[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]17/10/2016[/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/13/2016[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]23/10/2016[/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/14/2016[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]24/10/2016[/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/14/2016[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]25/12/2016[/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/15/2016[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]26/12/2016[/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/15/2016[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]27/12/2016[/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/15/2016[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]28/12/2016[/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/16/2016[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]29/12/2016[/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/16/2016[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/1/2017[/TD]

[TD="align: center"]17[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/16/2016[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]10/4/2017[/TD]

[TD="align: center"]18[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/17/2016[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]11/4/2017[/TD]

[TD="align: center"]19[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/17/2016[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]12/4/2017[/TD]

[TD="align: center"]20[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/18/2016[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]13/04/2017[/TD]

[TD="align: center"]21[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/18/2016[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]16/04/2017[/TD]

[TD="align: center"]22[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/19/2016[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]17/04/2017[/TD]

[TD="align: center"]23[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/19/2016[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]18/04/2017[/TD]

[TD="align: center"]24[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/20/2016[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]2/5/2017[/TD]

[TD="align: center"]25[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/20/2016[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]30/05/2017[/TD]

[TD="align: center"]26[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/21/2016[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]31/05/2017[/TD]

[TD="align: center"]27[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/21/2016[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/6/2017[/TD]

[TD="align: center"]28[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/22/2016[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]20/09/2017[/TD]

[TD="align: center"]29[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1/22/2016[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]21/09/2017[/TD]

</tbody>
Sheet7

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A5[/TH]
[TD="align: left"]=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))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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

Forum statistics

Threads
1,223,237
Messages
6,170,928
Members
452,366
Latest member
TePunaBloke

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