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 can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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
You must log in or register to reply here.

Similar threads

W
  • Solved
Formula Error: Error applying formula with VBA even though it works manually
Replies
3
Views
97
6StringJazzer
6StringJazzer
R
  • Solved
Ignore blanks for a SUM
Replies
2
Views
115
Rex987456
R
A
  • Question Question
Get enddatetime if X no. of working hours is added to startdatetime
Replies
1
Views
439
myall_blues
myall_blues
W
  • Question Question
Copy formula across 28 day dynamic shift calendar
Replies
4
Views
334
Wolfgang17
W
J
  • Question Question
filling cells with using values in helper cell
Replies
0
Views
177
jbrown021286
J

Forum statistics

Threads
1,223,911
Messages
6,175,334
Members
452,636
Latest member
laura12345

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