This seems to work:
Where:
A1 = 1/5/2009 = the first Monday of January, 2009
A2 = 1/19/2009 = the second Monday of January, 2009
A3 = 1/31/2009 = EOMONTH for January, 2009
A4 = 2/2/2009 = first Monday for February, 2009
Cell A3
=IF(A2=EOMONTH(A2,0),IF(A1+14<>EOMONTH(A2,0),A1+14,A2+14),IF(MONTH(A2)=MONTH(A2+14),A2+14,EOMONTH(A2,0)))
Cell A3 = 1/31/2009
Cell A4 = 2/2/2009
Cell A5 = 2/16/2009
etc.
etc.
This fills all cells of a column with dates corresponding to the first
Monday of January, 2009 and every-other Monday thereafter, including the
correct EOMONTH for each month with no duplicates for the end of the
month.
I have not figured out how to "kick it off", so I have to manually fill in
the first two dates. From then on, the formula works. I tried to shorten
the formula using an AND function but ran out of patience.
Of course, the equation will work with any day of the week and any week of
the month to produce biweekly dates, you just have to (with this formula)
kick it off with the appropriate first two manual dates.
Banks don't work on Holidays, and this formula will not shift a date for a
Holiday. It produces May 25, a Holiday, instead of May 26, when the bank
is
open. Some of the EOMONTHs are on weekends as well. I will have to wait
and
see what day the bank automatically debits my loan for the end of the
month
LPP. If the computer doesn't work on weekends, I will have to try to move
EOMONTH payments back to the last workday.
Any ideas how I might do that, if required?
--
staplers
Fred Smith said:
I haven't been able to come up with a solution to the problem. What I
would
do is either live with it, or convert the dates to values (copy>paste
special... values), and then delete the duplicate rows.
Regards,
Fred.
staplers said:
O.K. I figured out your formula and what I did wrong when I made it
fit
my
cell numbers. After I corrected my mistake, it almost works
perfectly -
unless the last Monday in the month is the 17 th and the EOMONTH is the
31st
. When this happens, the formula calculates one date on the 17, then
the
EOMONTH for the 31, and then 17 + 14 = 31 again. It would do the same
thing
for a 16th and 30th combination.
Through March 2013, this happens for August 2009 and January 2011.
Your formula almost did the trick, with this exception. I have tried
to
use
the double date, since it means I am making a payment on the date as
well
as
a LPP payment on that date, but a single date would work better.
Can you figure out how to eliminate either the last Monday or the
EOMONTH
in
the case where they coexist?
Thanks for your help.
--
staplers
:
This doesn't work as I need. [re:
"=IF(A2=EOMONTH(A2,0),A1+14,IF(MONTH(A2)=MONTH(A2+14),A2+14,EOMONTH(A2,0)))"]
For my loan:
D10 = Loan_Start = 3/20/09
A19 = Payment #1 = 3/27/09
A19 – A29 = Payments 1 – 11, irregular payments trying to set a
schedule
A30 = first regular, scheduled biweekly payment = Monday, June 8 =
Payment 12
Everything following should be every other Monday + every EOMONTH
Your formula gives 3 dates per month where 2 Mondays exist in the
month,
but they are every 14th and every 28th + every EOMONTH. I need them
to
be
every Monday + every EOMONTH. Your formula never gives 3 Mondays +
EOMONTH,
but two months should have these 4 dates.
I can ignore payments 1 -11 since they are not on a schedule, but I
need
to
begin my schedule by specifying the date for payment 12, Monday, June
8,
and
then follow a biweekly schedule + EOMONTH from then on.
Is this possible to do?
--
staplers
:
As you said "anything is welcome", here's a formula to do what you
want.
Assumes starting date is in a2:
=IF(A2=EOMONTH(A2,0),A1+14,IF(MONTH(A2)=MONTH(A2+14),A2+14,EOMONTH(A2,0)))
Regards,
Fred
I can fill cells of a column with all 26 recurring biweekly Mondays
(for
instance) in the year. I can fill cells of a column with all 12
EOMONTHs
in
the year. But I cannot do both at once. How can I create a
column
that
lists selected biweekly days/dates including EOMONTH for all 12
months?
10
months would have two biweekly days/dates plus one EOMONTH (3
dates)
and
two
months would have three biweekly days/dates plus one EOMONTH (4
dates).
This
is for an Open Ended loan I am trying to track with an
amortization
plan
where I make 26 payments per year, every other Monday, and a Loan
Protection
Plan charge is added to my loan the last day of every month (No, I
don't
like
being forced to pay interest on the extra loan amount, but I
cannot
pre-pay
it monthly.) I want a “global†formula using defined ranges, not
one
that
sequences from the previous/last cell to test and fill the
next/succeeding
cell. However, anything is welcome.
