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 turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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

L
  • Solved
Fix calendar setup
Replies
7
Views
97
MARK858
MARK858
W
  • Solved
Formula Error: Error applying formula with VBA even though it works manually
Replies
3
Views
122
6StringJazzer
6StringJazzer
R
  • Solved
Ignore blanks for a SUM
Replies
2
Views
121
Rex987456
R
A
  • Question Question
Get enddatetime if X no. of working hours is added to startdatetime
Replies
1
Views
446
myall_blues
myall_blues
W
  • Question Question
Copy formula across 28 day dynamic shift calendar
Replies
4
Views
351
Wolfgang17
W

Forum statistics

Threads
1,224,828
Messages
6,181,209
Members
453,022
Latest member
RobertV1609

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