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_Start)+(Pay_Num)*12/Num_Pmt_Per_Year,DAY(Loan_Start)),"")

On Mon, 31 Oct 2005 00:14:02 -0800, "Dreamstar_1961"
<[email protected]> 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

"Ron Rosenfeld" wrote:

> On Mon, 31 Oct 2005 00:14:02 -0800, "Dreamstar_1961"
> <[email protected]> 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.

On Mon, 31 Oct 2005 21:29:02 -0800, "Dreamstar_1961"
<[email protected]> 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