M

#### minyeh

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+B2+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(MATCH(A2+B2+2,$E:$E,

0)),A2+B2+3,A2+B2+2)))),IF(WEEKDAY(A2+B2,2)<6,A2+B2,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(ISNUMBER(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