Hi,

I'm confused - "Biweekly dates must not be every 12th or every 15th, for
instance, but every Monday or every Wednesday, for instance."

Since weekly or bi weekly can never fall on the same day of every month,
what does this statement mean? Also, if Monday is the 12th or Wednesday is
the 15th in one month what's going on?

And of course "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."
doesn't tell us what you want to do here. Suppose the holiday is Monday and
the date falls on Sunday, which way do you go?

I could continue, but instead why not go back and post a clear, complete and
concise question. The devil is in the details.

Regarding switching between bi-weekly and weekly try this basic idea

=G1+IF(C2="Bi",14,7)

Enter the Start date in G1, enter the text Bi or Weekly in C2, then enter
this formula in G2 and copy it down. When you change the word in C2 you will
switch from bi-weekly to weekly date spacing in column G.


--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"staplers" wrote:

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