Finding available payment date

[tara]

I need help finding a formula that will help my find an available payment
date according to the date that someone comes in to sign a contract. Example.
Joe comes in 1/27/09. He needs to begin making payments 2 months from that
day on the next available payment day which is the 1st (4/1/09). If he comes
in on the 2/6/09 the next available payment day is tenth (4/10/09).

I want to be able to plug in a date and the next availble payment date
should pop up in another cell.

People who join anytime betweent he 26th to the 1st begin their payments on
the 1st. People who come in and join the 2nd to the 10th begin their
payments on the 10th. and so on using the 15th, 20th and 25th.

I have seen it done, but have no clue where to begin. Thanks for any help. T

 

[Ron@Buy]

Tara
Just a few questions:
Is the inconsistancy correct? How does it effect the 2 month rule?
26th to 1st - number of days vary with each month - from 7 days to 4 days
2nd to 10th is 9 days
11th to 15th is 5 days
16th to 20th is 5 days
21st to 25th is 5 days
26th to 26th is 1 day
Does the day of the week matter i.e. Saturday or Sunday as a start paying day?
Clear these points up and we can progress further

 

[joeu2004]

If I understand your requirements correctly, then assuming A1 contains
the contract date, the first payment date would be:

=date(year(A1), 2+month(A1),
vlookup(day(A1),{1,1;2,10;11,15;16,20;21,25;26,1},2))

The VLOOKUP table is "contract date, first-payment date":

Contract First
Date >= Payment

1 1
2 10
11 15
16 20
21 25
26 1


----- original posting -----

 

[tara]

Thanks Joe. That is a really cool formula. The only problem I have is when
I put in todays date(1/29) it gives me a 3/1/09 start date which of course
works with the formula. What I really needed (but is really hard to explain)
is it to spit out is a 4/1 start date because the member has paid the first
two months (Feb 1 to March 31), he is getting 1/29 to 1/31 for "free", his
payments need to begin 4/1/09. It works perfect for 1st through the 25th,
just not the 26th to the 31st. Is it possible to make that one do something
the others to not?

Thank you so much for the help, this is wayyyy beyond regular(boring)
spreadsheet stuff I do daily.

 

[joeu2004]

The only problem I have is when I put in todays date(1/29)
[...] What I really needed (but is really hard to explain)
is it to spit out is a 4/1 start date

Your explanations are fine. I just wasn't paying attention. Sorry.
Try the following:

=date(year(A1), month(A1)+2+(day(A1)>25),
vlookup(day(A1),{1,1;2,10;11,15;16,20;21,25;26,1},2))

The formula yields the following contract date and first-payment date
combinations. Let me know if this is right or wrong.

Contract Payment
Date Date
12/ 1/2008 2/ 1/2009
12/ 2/2008 2/10/2009
12/10/2008 2/10/2009
12/11/2008 2/15/2009
12/15/2008 2/15/2009
12/16/2008 2/20/2009
12/20/2008 2/20/2009
12/21/2008 2/25/2009
12/25/2008 2/25/2009
12/26/2008 3/ 1/2009
12/31/2008 3/ 1/2009


----- original posting -----

 

[tara]

Works perfectly. Thanks so much!

The only problem I have is when I put in todays date(1/29)
[...] What I really needed (but is really hard to explain)
is it to spit out is a 4/1 start date

Your explanations are fine. I just wasn't paying attention. Sorry.
Try the following:

=date(year(A1), month(A1)+2+(day(A1)>25),
vlookup(day(A1),{1,1;2,10;11,15;16,20;21,25;26,1},2))

The formula yields the following contract date and first-payment date
combinations. Let me know if this is right or wrong.

Contract Payment
Date Date
12/ 1/2008 2/ 1/2009
12/ 2/2008 2/10/2009
12/10/2008 2/10/2009
12/11/2008 2/15/2009
12/15/2008 2/15/2009
12/16/2008 2/20/2009
12/20/2008 2/20/2009
12/21/2008 2/25/2009
12/25/2008 2/25/2009
12/26/2008 3/ 1/2009
12/31/2008 3/ 1/2009


----- original posting -----

Thanks Joe. That is a really cool formula. The only problem I have is when
I put in todays date(1/29) it gives me a 3/1/09 start date which of course
works with the formula. What I really needed (but is really hard to explain)
is it to spit out is a 4/1 start date because the member has paid the first
two months (Feb 1 to March 31), he is getting 1/29 to 1/31 for "free", his
payments need to begin 4/1/09. It works perfect for 1st through the 25th,
just not the 26th to the 31st. Is it possible to make that one do something
the others to not?

Thank you so much for the help, this is wayyyy beyond regular(boring)
spreadsheet stuff I do daily.