identifying the next working day after weekends and/or holidays

[staplers]

I make automatic loan payments every other week on a Monday, and, in
addition, automatic loan insurance payments on the last day of the month. If
any of these dates fall on a weekend or a Holiday, the loan institution moves
the payment date forward one working date. The next automatic loan payment
date ignores any previous shift and is still made two weeks later on a
Monday. When the automatic loan payment fell on Monday,1/18/10, MLK day, the
payment was moved forward to the next working day, 1/19/10. The next
automatic loan payment ignored this shift and was made two weeks from 1/18/10
on Monday, 2/1/10. The next automatic loan payment was scheduled to be made
two weeks from 2/1/10 on Monday, but fell on 2/15/10, President's day. This
payment was moved forward to the next working day, 2/16/10. The next
automatic loan payment will ignore this shift and be made two weeks from
2/15/10 on Monday, 3/1/10.

I need to create a column of dates that will track the lending institution's
movement of scheduled payment dates. I am currently able to create a column
of loan payment dates two weeks/14 days apart, including also the insurance
payment end-of-month date, and can make the loan payment and the insurance
payment on the same date if they coincide, but I cannot skip weekends and
Holidays and make payments on the "next working date". As countless others
before me have said, if the Workday function just did EXACTLY what its
definition says, and allowed <>Workday, I could do this easily. I have two
arrays set up for Holidays and for Weekends that I can use as lookups,
although I know little of array functions.

If someone can help me to get the date column working as I need, I can do
the rest.

Thanks for any help or suggestions.

 

[Fred Smith]

One solution is to do away with Workday, as it's not working for you. As
loan payments are always on Mondays, the only issue is whether that Monday
is a holiday. If so, the payment is due on the next day. So try:
=IF(COUNTIF(D$2$6,A2)>0,A2+1,A2)

where A2 is the calculated payment date, and D26 is your list of holidays.

Regards,
Fred

 

[Ron Rosenfeld]

I make automatic loan payments every other week on a Monday, and, in
addition, automatic loan insurance payments on the last day of the month. If
any of these dates fall on a weekend or a Holiday, the loan institution moves
the payment date forward one working date. The next automatic loan payment
date ignores any previous shift and is still made two weeks later on a
Monday. When the automatic loan payment fell on Monday,1/18/10, MLK day, the
payment was moved forward to the next working day, 1/19/10. The next
automatic loan payment ignored this shift and was made two weeks from 1/18/10
on Monday, 2/1/10. The next automatic loan payment was scheduled to be made
two weeks from 2/1/10 on Monday, but fell on 2/15/10, President's day. This
payment was moved forward to the next working day, 2/16/10. The next
automatic loan payment will ignore this shift and be made two weeks from
2/15/10 on Monday, 3/1/10.

I need to create a column of dates that will track the lending institution's
movement of scheduled payment dates. I am currently able to create a column
of loan payment dates two weeks/14 days apart, including also the insurance
payment end-of-month date, and can make the loan payment and the insurance
payment on the same date if they coincide, but I cannot skip weekends and
Holidays and make payments on the "next working date". As countless others
before me have said, if the Workday function just did EXACTLY what its
definition says, and allowed <>Workday, I could do this easily. I have two
arrays set up for Holidays and for Weekends that I can use as lookups,
although I know little of array functions.

If someone can help me to get the date column working as I need, I can do
the rest.

Thanks for any help or suggestions.

The algorithm using the WORKDAY function is straightforward to handle your
problem. It's a matter of computing the unadjusted date; subtract one day from
that; then add one Workday. No need for arrays or lookups, although you do
need a table of Holiday dates.

So considering your column of regular loan payments, if you have a legitimate
payment date in A1 (e.g. 1/4/2010) then

A1: 1/4/2010
A2: =WORKDAY($A$1+ROWS($1:1)*14-1,1,holidays)

and fill down.

Similarly for your end of month payments:

B1: =WORKDAY(EOMONTH($A$1,ROWS($1:1)-1)-1,1,holidays)

and fill down
--ron