I have a table with
Column A = Invoice Date
Column B = Days of Credit Granted
Column C = Payment Due Date
Column E = List of Public Holiday
with the first row as header and subsesquent rows as the actual data

what i want to do is to auto generate the payment due date of each
invoices so that it won't falls on weekends and public holiday. for
example, if [Invoice Date] + [Days of Credit Granted] falls on a
saturday, then the due date will be push forward 2 days to monday.

i came out with a formula which works pretty fine given that there's
no consecutive non-working days of more than 4 days (which would be
quite rare). i wonder if there's an easier and cleaner way to do this
=IF(A2="","",IF(ISNUMBER(MATCH(A2+B2,$E:$E,0)),IF( ISNUMBER(MATCH
(A2+B2+1,$E:$E,0)),IF(WEEKDAY(A2+B2+2,2)<6,A2+B2+2 ,CHOOSE(WEEKDAY
(A2+B2+2,2)-5,IF(ISNUMBER(MATCH(A2+B2+4,$E:$E,0)),A2+B2+5,A2+B 2+4),IF
(ISNUMBER(MATCH(A2+B2+3,$E:$E,0)),A2+B2+4,A2+B2+3) )),IF(WEEKDAY
(A2+B2+1,2)<6,A2+B2+1,CHOOSE(WEEKDAY(A2+B2+1,2)-5,IF(ISNUMBER(MATCH
(A2+B2+3,$E:$E,0)),A2+B2+4,A2+B2+3),IF(ISNUMBER(MA TCH(A2+B2+2,$E:$E,
0)),A2+B2+3,A2+B2+2)))),IF(WEEKDAY(A2+B2,2)<6,A2+B 2,CHOOSE(WEEKDAY
(A2+B2,2)-5,IF(ISNUMBER(MATCH(A2+B2+2,$E:$E,0)),IF(ISNUMBER( MATCH
(A2+B2+3,$E:$E,0)),A2+B2+4,A2+B2+3),A2+B2+2),IF(IS NUMBER(MATCH
(A2+B2+1,$E:$E,0)),IF(ISNUMBER(MATCH(A2+B2+2,$E:$E ,
0)),A2+B2+3,A2+B2+2),A2+B2+1)))))


Thanks for ur time : )

Regards,
Min Yeh