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.
CodePudding user response：
I would use datedif() like this:
=IFERROR(IF(DATEDIF(C2,NOW(),"d")<7,"N","Y"),"N")
Or, correcting the other answer:
=IF(NETWORKDAYS(C2,NOW())>7,"Y","N")
CodePudding user response：
You can use this formula: =IF(NETWORKDAYS(C4;B4)>7;"Y";"N")