Generate Payment Due Date to exclude Weekends and Public Holiday

[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

 

[Bob Phillips]

Does this do it

=IF(A2="","",WORKDAY(A2,B2,E:E))

HTH

Bob

 

[Ron Rosenfeld]

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

=if(a2="","",WORKDAY(A2+B2-1,1,E2:E100))


I would use a smaller range for holidays than the entire column.

If this formula returns the #NAME error, and you are using a version of Excel
prior to 2007, look at HELP for the WORKDAY function for instructions to
install the Analysis ToolPak.
--ron

 

[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

=if(a2="","",WORKDAY(A2+B2-1,1,E2:E100))

I would use a smaller range for holidays than the entire column.

If this formula returns the #NAME error, and you are using a version of Excel
prior to 2007, look at HELP for the WORKDAY function for instructions to
install the Analysis ToolPak.
--ron

thanks Bob and Ron for the reply, Ron's reply gives me the result i
wanted. It helps a lot : )

Maybe i didn't make myself very clear so Bob gets carried away, i'm
not calculating workdays, but the effective last due date of invoices.
Just for example, one of my customer buys a ton of coal on 1 Jan 2010
(fri), he was granted 30 days of credit, thus, the due date should be
1 Jan 2010 + 30 days = 31 Jan 2010 (sun), but it falls on a Sunday,
when my office is not operating. So, I allow the customer to pay on
the next working day, but the next day, 1 Feb 2010 (mon) happens to be
a public holiday here in Kuala Lumpur, Malaysia, so the last due date
is pushed another days forward to 2 Feb 2010 (tue). Instead of 2 Feb
2010, the first formula gives me 15 Feb 2010.

 

[Ron Rosenfeld]

thanks Bob and Ron for the reply, Ron's reply gives me the result i
wanted. It helps a lot : )

Glad to help. Thanks for the feedback.
--ron