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)),"")

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

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.

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