Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Date Format
What I want to know, is I have a loan Calculator. i want it to give me days
in a week, where as it currently show 1 date a month repeated for the number of week for the month This is what it showing at the moment, This is what I want it to show 19/02/2005 19/02/2005 19/02/2005 26/02/2005 19/02/2005 5/03/2005 19/02/2005 12/03/2005 19/03/2005 19/03/2005 19/03/2005 26/03/2005 19/03/2005 2/04/2005 19/03/2005 9/04/2005 19/04/2005 16/04/2005 19/04/2005 23/04/2005 It's fine for 12 monthly payments but not for weekly, this is the fomula used need to know how to change =IF(Pay_Num<"",DATE(YEAR(Loan_Start),MONTH(Loan_S tart)+(Pay_Num)*12/Num_Pmt_Per_Year,DAY(Loan_Start)),"") |
#2
|
|||
|
|||
Date Format
On Mon, 31 Oct 2005 00:14:02 -0800, "Dreamstar_1961"
wrote: What I want to know, is I have a loan Calculator. i want it to give me days in a week, where as it currently show 1 date a month repeated for the number of week for the month This is what it showing at the moment, This is what I want it to show 19/02/2005 19/02/2005 19/02/2005 26/02/2005 19/02/2005 5/03/2005 19/02/2005 12/03/2005 19/03/2005 19/03/2005 19/03/2005 26/03/2005 19/03/2005 2/04/2005 19/03/2005 9/04/2005 19/04/2005 16/04/2005 19/04/2005 23/04/2005 It's fine for 12 monthly payments but not for weekly, this is the fomula used need to know how to change =IF(Pay_Num<"",DATE(YEAR(Loan_Start),MONTH(Loan_ Start)+(Pay_Num)*12/Num_Pmt_Per_Year,DAY(Loan_Start)),"") You could try the formula below. It will handle Num_Pmt_Per_Year of 12, 26 or 52 (monthly, biweekly, weekly). It will not handle bi-monthly payments as that may depend on your lenders policy for the dates. In addition, I made a modification for the monthly payment portion because your formula gives the following results if Loan_Start = 31 Jan 05: 03 Mar 05 31 Mar 05 01 May 05 31 May 05 01 Jul 05 31 Jul 05 31 Aug 05 01 Oct 05 31 Oct 05 and I would think (at least in the US) the following would be more "logical" 28 Feb 05 31 Mar 05 30 Apr 05 31 May 05 30 Jun 05 31 Jul 05 31 Aug 05 30 Sep 05 31 Oct 05 In any event, try this and let me know what you think: =IF(OR(Num_Pmt_Per_Year=26,Num_Pmt_Per_Year=52), Loan_Start+52*7*Pay_Num/Num_Pmt_Per_Year,IF( Num_Pmt_Per_Year=12,MIN(DATE(YEAR(Loan_Start), MONTH(Loan_Start)+Pay_Num,DAY(Loan_Start)),DATE( YEAR(Loan_Start),MONTH(Loan_Start)+Pay_Num+1,0)))) --ron |
#3
|
|||
|
|||
Date Format
"Ron Rosenfeld" wrote: On Mon, 31 Oct 2005 00:14:02 -0800, "Dreamstar_1961" wrote: What I want to know, is I have a loan Calculator. i want it to give me days in a week, where as it currently show 1 date a month repeated for the number of week for the month This is what it showing at the moment, This is what I want it to show 19/02/2005 19/02/2005 19/02/2005 26/02/2005 19/02/2005 5/03/2005 19/02/2005 12/03/2005 19/03/2005 19/03/2005 19/03/2005 26/03/2005 19/03/2005 2/04/2005 19/03/2005 9/04/2005 19/04/2005 16/04/2005 19/04/2005 23/04/2005 It's fine for 12 monthly payments but not for weekly, this is the fomula used need to know how to change =IF(Pay_Num<"",DATE(YEAR(Loan_Start),MONTH(Loan_ Start)+(Pay_Num)*12/Num_Pmt_Per_Year,DAY(Loan_Start)),"") You could try the formula below. It will handle Num_Pmt_Per_Year of 12, 26 or 52 (monthly, biweekly, weekly). It will not handle bi-monthly payments as that may depend on your lenders policy for the dates. In addition, I made a modification for the monthly payment portion because your formula gives the following results if Loan_Start = 31 Jan 05: 03 Mar 05 31 Mar 05 01 May 05 31 May 05 01 Jul 05 31 Jul 05 31 Aug 05 01 Oct 05 31 Oct 05 and I would think (at least in the US) the following would be more "logical" 28 Feb 05 31 Mar 05 30 Apr 05 31 May 05 30 Jun 05 31 Jul 05 31 Aug 05 30 Sep 05 31 Oct 05 In any event, try this and let me know what you think: =IF(OR(Num_Pmt_Per_Year=26,Num_Pmt_Per_Year=52), Loan_Start+52*7*Pay_Num/Num_Pmt_Per_Year,IF( Num_Pmt_Per_Year=12,MIN(DATE(YEAR(Loan_Start), MONTH(Loan_Start)+Pay_Num,DAY(Loan_Start)),DATE( YEAR(Loan_Start),MONTH(Loan_Start)+Pay_Num+1,0)))) --ron Thanks Ron that worked, the only problem, is I had a start date, which it took the next date along, I got around it by putting in the date the week before. |
#4
|
|||
|
|||
Date Format
On Mon, 31 Oct 2005 21:29:02 -0800, "Dreamstar_1961"
wrote: Thanks Ron that worked, the only problem, is I had a start date, which it took the next date along, I got around it by putting in the date the week before. An alternative, if you did not want to do that, would be to modify the Loan_Start factor in the equation, to subtract the appropriate length of time (related to Num_Pmts... ). But your method works fine, too. Glad to help. Thanks for the feedback. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change general format to US date format | Excel Discussion (Misc queries) | |||
Customized Date Format | Excel Worksheet Functions | |||
Compare dates (one cell not in date format) | Excel Discussion (Misc queries) | |||
How do I keep the date from changing format in a mail merge? | Excel Discussion (Misc queries) | |||
USING THE DATE FORMAT IN EXCEL | Excel Discussion (Misc queries) |