Date Format

"Ron Rosenfeld" wrote:

> On Mon, 31 Oct 2005 00:14:02 -0800, "Dreamstar_1961"
> <Dreamstar_1961@discussions.microsoft.com> 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"
<Dreamstar1961@discussions.microsoft.com> 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