CODEWITHSUNDEEP
exceldaterecursionexcel-formulaiteration
amdN
amdN

Reputation: 11

Formula to calculate next date excluding list of dates

Can someone please help with formula to calculate the next available date excluding list of specific dates (blocked dates for some event). In the image attached, when a Plan date in column A is entered, it should calculate the invoice date based on 1. Earliest invoice date is event date closest to (Plan date + 5 days). 2. If the invoice date calculated is one of the blocked dates, the invoice date should be set to next working date

Step 1 above is calculated as shown in formula,

=MIN(IF($B$2:$B$8-($A2+5)>=0,$B$2:$B$8))

For Step 2 any help is appreciated. When tried Vlookup on calculated invoice date to match the value in blocked dates - once it calculates next day. But when calculated next day is also blocked day, it fails. For example, in first data row, invoice date based in step 1 is 20-Jan-16 which is blocked date.

In step 2, if VLOOKUP is used to match 20-Jan-16 and increment by 1 day, it gives 21-Jan-16 which is again a blocked date. Any suggestions for recursion/iterative calculation to be done?

Sample data

    OP's sample data

The blocked dates are fairly large list for a year and in random order.

Upvotes: 1

Views: 1259

Answers (1)

user4039065
user4039065

Reputation:

The WORKDAY.INTL function¹ should esspecially effective for this. Not only can you specify a range of holidays to be used as blocked dates, but you can specify a special weekend string that identifies the days to be regarded as weekends. Since you do not want to exclude any other days-of-the-week, this will be simply "0000000".

In E2 as,

=IFERROR(WORKDAY.INTL(AGGREGATE(15, 6, (B$2:INDEX(B:B, MATCH(1E+99,B:B )))/(B$2:INDEX(B:B, MATCH(1E+99,B:B ))-(A2+5)>=0), 1)-1, 1, "0000000", C$2:INDEX(C:C, MATCH(1E+99,C:C ))), "")

Since we are dealing with second generation functions, I changed your MIN(IF(... array formula to an AGGREGATE² function which does not have to be array entered and used a wrapping IFERROR function to avoid displaying a #DIV/0! error in E8.

Fill down as necessary.

      workday_intl_aggregate

¹ The WORKDAY.INTL function¹ was introduced with Excel 2010. It is not available in earlier versions.

² The AGGREGATE function was introduced with Excel 2010. It is not available in earlier versions.

Upvotes: 2

Related Questions