| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SUMPRODUCT to return date of last payment? | Excel Discussion (Misc queries) | |||
| Starting payment when increase rate is known & total payment is kn | Excel Worksheet Functions | |||
| Payment cell populated based on date formula | Excel Discussion (Misc queries) | |||
| set payment date 28 days after following friday | Excel Discussion (Misc queries) | |||
| calculate payment with first payment due date variable? | Excel Worksheet Functions | |||
Hybrid Mode
