I have a loan with biweekly payments. An insurance charge (Loan Protection
Plan) is added to the principal the last day of the month. If this date is a
weekend or holiday, the payment may be shifted back to the last working day,
or forward to the next working day. I need to create a column of dates that:

1. enables biweekly or weekly dates, at my choice, beginning on the date of
my choice. (I can already do biweekly)
Biweekly dates must not be every 12th or every 15th, for instance, but every
Monday or every Wednesday, for instance. (I can already do this)
Dates must be shifted if they fall on a holiday. (I cannot do this currently)
2. allows 2 to 3 biweekly dates per month. (I can already do this)
3. allows the end of the month to be added to the other 2 -3 dates in
sequential order. (I can already do this)
4. creates only one single date where the EOM falls on one of the other
scheduled dates. (I can already do this)
5. shifts an EOM, when it falls on a weekend, to the previous last working
day or to the next working day, at my choice.
6. combines all of the above into one formula that creates a column of dates
that can be either weekly or biweekly, with EOM added, where no biweekly
payment nor EOM payment falls on a weekend or holiday, where the EOM date
does not duplicate another scheduled date, and where I can use reference cell
values to select weekly or biweekly payments, or shift to future or past
working dates.

If I could figure out how to do 5 and combine it with the rest, I would have
6.

My formula for 1+2+3+4 is (from somewhere in the date sequence - not the
first date - does NOT adjust for holidays:):

=IF(AND(Pay_Num<"",B33=EOMONTH(B33,0)),IF(B32+14< EOMONTH(B33,0),B32+14,B33+14),IF(MONTH(B33)=MONTH (B33+14),B33+14,EOMONTH(B33,0)))

where Pay_Num = the Named Range of the column of payment numbers
(and Pay_Date = the Named Range of the column of dates I am trying to create)

I have approached the WORKDAY function from numerous angles and it will not
perform 5. The WORKDAY function is mislabeled by MS and unfortunately misses
the opportunity to be a great function and fill a gaping hole €“ if only it
would perform exactly as defined.

I have received help from the community on the two separate problems, but
not on the complete solution. All responses were helpful and gave me some
things to experiment with, but no joy. I am not trying to solve a business
problem, although this is for a personal loan for a startup business. I am
trying to learn Excel and create a very flexible amortization schedule for
personal use.

Any help will be appreciated.

--
staplers