M
minyeh
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+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
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