Help to create biweekly dates excluding nonworking days

[staplers]

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.

 

[Shane Devenshire]

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.

 

[staplers]

All the days and dates mentioned previously had no reference to a real
calendar, they were just to illustrate points. Nothing said that every
Monday would be on the 12th or every Wednesday would be on the 15th. It said
that I wanted the biweekly dates to be a fixed day (Monday in my specific
case), but not a fixed date such as 12th or 15th. I saw one biweekly
calendar that did this.

Definitions:

working days - non-weekend and non-holiday
non-working days - weekends and holidays
weekends - Saturday and Sunday
holidays - defined/named range called Holidays
working days and non-working days are completely exclusive of one another

A.The Bank's only two loan requirements:
1. one payment per month on the loan
2. one insurance premium payment per month, on "the last day of the month"

B.My only two requirements (and one additional preference further down in
the discussion):
1. make loan payments every other week (on Monday in this case)
2. make one insurance premium payment on the last day of the month
this amounts to 26 loan payments and 12 insurance payments, 3 payments per
month for 10 months and 4 payments per month for two months

C.Non-working day requirements: All other requirements depend upon the
calendar and upon the bank working days (and one additional preference
further down in the discussion):
1. any loan payment that falls on a holiday must be shifted to the next
working day
2. any insurance premium payment that falls on a weekend or a holiday must
be shifted to the next working day (non holiday).

B.3. It is possible that the day of the week selected for loan payment will
also fall on the last day of the month (August 31 this year) and I prefer to
not have two days created. I prefer for the formula to create a single date
for these two events.

C.3. I am assuming that the bank will shift loan or insurance premium
payments to the NEXT working day any time there is a conflict with a
non-working day, since this occurred on the holiday Monday, May 25. The bank
shifted this to Tuesday, May 26. However, at this time I don't know what
will happen if the date falls on a weekend, so I would prefer to be able to
code the formula to accept input from a named cell or fixed cell location to
shift dates forward or backward. I don't see any calendar instances of it,
but if an insurance premium payment falls on a weekend and shifting forward
or backward falls on a holiday (non working day), it must shift further to a
working day.

Soapbox:
I can currently do all except shift dates which is the hardest part. If the
WORKDAY function worked as advertised, it would easily satisfy this
requirement. WORKDAY leaves a gaping hole in the date functions by not
delivering according to its definition.

Commentary:
Having one versatile formula to perform all the above would allow a sort of
universal home spreadsheet that could follow and predict (the most important
feature) almost any standard mortgage or any open-ended line-of-credit loan.
I'm not into "high finance" but I think this formula would be very beneficial
to me as a home user. I am sure that the formula that I included on the
first post can be compacted by someone with good Excel skills, I am just a
journeyman and welcome improvement(s) to the formula. There also appears to
be a conflict that randomly causes my existing sequence of dates to "lose
data" sometimes when the spreadsheet is opened, and it reports "=#N/A" in all
the date cells. I have to retrieve a good formula from another spreadsheet
and copy it to the current spreadsheet.

My personal requirements are simple (B above). It gets complex considering
the bank schedules and calendar conflicts.

Thanks for any help.