Finding available payment date
Works perfectly. Thanks so much!
"joeu2004" wrote:
On Jan 29, 12:43 pm, tara wrote:
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 -----
On Jan 29, 12:43 pm, tara wrote:
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" wrote:
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 -----
On Jan 29, 8:04 am, tara wrote:
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
|