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.
--
staplers

Hi,

I don't think you can do this with a simple fill command, you could do it
using VBA.

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

Cheers,
Shane Devenshire


"staplers" wrote:

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.
--
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+1 4),A2+14,EOMONTH(A2,0)))

Regards,
Fred

"staplers" wrote in message
...
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.
--
staplers

This doesn't work as I need. [
"=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


"Fred Smith" wrote:

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+1 4),A2+14,EOMONTH(A2,0)))

Regards,
Fred

"staplers" wrote in message
...
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.
--
staplers

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


"staplers" wrote:

This doesn't work as I need. [
"=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


"Fred Smith" wrote:

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+1 4),A2+14,EOMONTH(A2,0)))

Regards,
Fred

"staplers" wrote in message
...
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.
--
staplers

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 (copypaste
special... values), and then delete the duplicate rows.

Regards,
Fred.

"staplers" wrote in message
...
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


"staplers" wrote:

This doesn't work as I need. [
"=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


"Fred Smith" wrote:

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+1 4),A2+14,EOMONTH(A2,0)))

Regards,
Fred

"staplers" wrote in message
...
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.
--
staplers

This seems to work:

Whe

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" wrote:

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 (copypaste
special... values), and then delete the duplicate rows.

Regards,
Fred.

"staplers" wrote in message
...
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


"staplers" wrote:

This doesn't work as I need. [
"=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


"Fred Smith" wrote:

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+1 4),A2+14,EOMONTH(A2,0)))

Regards,
Fred

"staplers" wrote in message
...
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.
--
staplers

I agree with you about running out of patience. It depends what you want to
achieve. If you have to know the exact amortization schedule that the bank
will apply, you will need to check with them to see what the rules are. I
expect, however, you'll find this is a very frustrating process.

If you only need something that's good enough, you have it. To track the
loan, I would just adjust the dates manually as each one goes through your
bank.

Regards,
Fred.

"staplers" wrote in message
...
This seems to work:

Whe

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" wrote:

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 (copypaste
special... values), and then delete the duplicate rows.

Regards,
Fred.

"staplers" wrote in message
...
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


"staplers" wrote:

This doesn't work as I need. [
"=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


"Fred Smith" wrote:

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+1 4),A2+14,EOMONTH(A2,0)))

Regards,
Fred

"staplers" wrote in message
...
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.
--
staplers