Check if date has exceeded 7 business days

Question

I have an Excel sheet with some date columns.

One column is an "arrival" date. The other is a "notice" date.

I need to create a formula (VLOOKUP or whatever) that can check if the notice date has exceeded 7 business days.

I created an extra column called "date check", which is where I'll create the formula.

As of right now, the sheet resembles this:

reference	arrival date	notice date	date check
test1	14-DEC-21	10-DEC-21
test2	07-DEC-21	03-DEC-21
test3	27-DEC-21	21-DEC-21
test4	07-DEC-21	30-NOV-21

I need to write a formula within column D that will check the values in column C to see if they have exceeded 7 business days from the date in column C.

If I use today's date (16-DEC-21), then the above test case should should mark that test2 and test4 have exceeded 7 business days, as follows:

reference	arrival date	notice date	date check
test1	14-DEC-21	10-DEC-21	N
test2	07-DEC-21	03-DEC-21	Y
test3	27-DEC-21	21-DEC-21	N
test4	07-DEC-21	30-NOV-21	Y

How can I make this work?

Edit

I found this formula:

=IFERROR(INDEX(I:I, AGGREGATE(15, 6, ROW($1:$22)/((I$1:I$22<=TODAY()-7)*(I$1:I$22>0)), ROW(1:1))), "")

But I'm not sure how to incorporate it into my situation.

Answer 1

You can use this formula: =IF(NETWORKDAYS(C4;B4)>7;"Y";"N")