Home |
Search |
Today's Posts |
#1
|
|||
|
|||
date formula
hi,
i've built a loan amortization schedule with payments due semi-annually. my problem is that the payment must be on the same day of the 6th and 12th month. for example payments due 2/12/05 and 8/12/05 or 9/23/05 and 3/23/06. the problem is that months have 30 or 31 days and then there's february and leap years too. is there a way to program this function so that manual entry payment dates can be eliminated? tia dennis |
#2
|
|||
|
|||
you could use data validation to supply a limited number of dates to
choose from. |
#3
|
|||
|
|||
On Fri, 11 Feb 2005 20:07:01 -0500, "dennis" wrote:
hi, i've built a loan amortization schedule with payments due semi-annually. my problem is that the payment must be on the same day of the 6th and 12th month. for example payments due 2/12/05 and 8/12/05 or 9/23/05 and 3/23/06. the problem is that months have 30 or 31 days and then there's february and leap years too. is there a way to program this function so that manual entry payment dates can be eliminated? tia dennis What do you want to happen if the first payment date is 8/30/2005, or 3/31/2005? If that is not an issue, the general formula, assuming your dates are in a column with no blank rows, would be: =DATE(YEAR(FirstPmtDate),MONTH(FirstPmtDate) +6*ROW()-ROW(FirstPmtDate)*6,DAY(FirstPmtDate)) However, if for a First Payment Date of 8/30/2005 you want the next payment to be due on the last day of February. and for 3/31/2005 you want the next payment to be on the last day of September, then it becomes more complicated. =IF(DAY(DATE(YEAR(FirstPmtDate),MONTH(FirstPmtDate )+6*ROW() -ROW(FirstPmtDate)*6,DAY(FirstPmtDate)))=DAY(FirstP mtDate),DATE( YEAR(FirstPmtDate),MONTH(FirstPmtDate)+6*ROW()-ROW( FirstPmtDate)*6,DAY(FirstPmtDate)),DATE(YEAR(First PmtDate), MONTH(FirstPmtDate)+6*ROW()-ROW(FirstPmtDate)*6,DAY( FirstPmtDate))-DAY(DATE(YEAR(FirstPmtDate),MONTH(FirstPmtDate) +6*ROW()-ROW(FirstPmtDate)*6,DAY(FirstPmtDate)))) --ron |
#4
|
|||
|
|||
On Fri, 11 Feb 2005 20:07:01 -0500, "dennis" wrote:
hi, i've built a loan amortization schedule with payments due semi-annually. my problem is that the payment must be on the same day of the 6th and 12th month. for example payments due 2/12/05 and 8/12/05 or 9/23/05 and 3/23/06. the problem is that months have 30 or 31 days and then there's february and leap years too. is there a way to program this function so that manual entry payment dates can be eliminated? tia dennis If you have installed (or can install) the Analysis Tool Pack, you can use a simpler formula than the one I previously posted: Again, with the payment dates in a column under FirstPmtDate and no blank rows: =EDATE(FirstPmtDate,(ROW()-ROW(FirstPmtDate))*6) If the EDATE function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in. How? On the Tools menu, click Add-Ins. In the Add-Ins available list, select the Analysis ToolPak box, and then click OK. If necessary, follow the instructions in the setup program. --ron |
#5
|
|||
|
|||
On Fri, 11 Feb 2005 22:08:55 -0600, "Fred Smith" wrote:
To get the next date 6 months out, use: =date(year(a1),month(a1)+6,day(a1)) Excel is smart enough to roll over to the next year if you're starting in months from Jul to Dec. Try 8/30/2005 in A1. --ron |
#6
|
|||
|
|||
ron, wouldn't there be a problem only if one were to begin their series with
the following 4: 8/29, 8/30, 8/31 or 2/29 ? All others (limited test) seem to work.. TIA, "Ron Rosenfeld" wrote in message ... On Fri, 11 Feb 2005 22:08:55 -0600, "Fred Smith" wrote: To get the next date 6 months out, use: =date(year(a1),month(a1)+6,day(a1)) Excel is smart enough to roll over to the next year if you're starting in months from Jul to Dec. Try 8/30/2005 in A1. --ron |
#7
|
|||
|
|||
On Sat, 12 Feb 2005 09:17:24 -0500, "Jim May" wrote:
ron, wouldn't there be a problem only if one were to begin their series with the following 4: 8/29, 8/30, 8/31 or 2/29 ? The problem will occur with the following dates: 29-Feb 31-Mar 31-May 29-Aug *unless subsequent year is a leap year 30-Aug 31-Aug 31-Oct 31-Dec --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date Formula | Excel Discussion (Misc queries) | |||
Date formula | Excel Discussion (Misc queries) | |||
Formula to determine a future date based on criteria | Excel Worksheet Functions | |||
Need help troubleshooting an array formula XLXP on Win2K | Excel Worksheet Functions | |||
How do I add a date formula to a cell but hide the contents with . | Excel Discussion (Misc queries) |