I have a spreadsheet where in one column (Column A) a user needs to insert a
year end date (eg: 30/06/2002) as the starting date of the loan. Then the
years increment below - Each cell below this starting cell needs to increase
yearly for the term of the loan.
So I need calculations for if there is a leap year, etc.
I have used =A10+365 but it doesn't calculate for leap years so I have to
insert the 366 on the leap years. Is there any way to ensure this is done by
a formula?
PLEASE NOTE: We use the date formula in Australia 31/03/2002.

Hi

When you enter start date into A2, then into A3 enter the formula
=DATE(YEAR($A$2)+ROW()-2,MONTH($A$2)+1,0)
, and format in any valid date format.
Copy A3 down.

--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )



"Jenny Potter" <(E-Mail Removed)> wrote in message
news:B0EFD3C4-EA95-48B8-8414-(E-Mail Removed)...
>I have a spreadsheet where in one column (Column A) a user needs to insert
>a
> year end date (eg: 30/06/2002) as the starting date of the loan. Then the
> years increment below - Each cell below this starting cell needs to
> increase
> yearly for the term of the loan.
> So I need calculations for if there is a leap year, etc.
> I have used =A10+365 but it doesn't calculate for leap years so I have to
> insert the 366 on the leap years. Is there any way to ensure this is done
> by
> a formula?
> PLEASE NOTE: We use the date formula in Australia 31/03/2002.
>

Thanks so much for the quick response - what does it all mean? I'd like to be
able to understand it.
ie: +ROW()-2
ie: MONTH($A$2)
ie: +1,0
Regards

"Arvi Laanemets" wrote:

> Hi
>
> When you enter start date into A2, then into A3 enter the formula
> =DATE(YEAR($A$2)+ROW()-2,MONTH($A$2)+1,0)
> , and format in any valid date format.
> Copy A3 down.
>
> --
> Arvi Laanemets
> ( My real mail address: arvi.laanemets<at>tarkon.ee )
>
>
>
> "Jenny Potter" <(E-Mail Removed)> wrote in message
> news:B0EFD3C4-EA95-48B8-8414-(E-Mail Removed)...
> >I have a spreadsheet where in one column (Column A) a user needs to insert
> >a
> > year end date (eg: 30/06/2002) as the starting date of the loan. Then the
> > years increment below - Each cell below this starting cell needs to
> > increase
> > yearly for the term of the loan.
> > So I need calculations for if there is a leap year, etc.
> > I have used =A10+365 but it doesn't calculate for leap years so I have to
> > insert the 366 on the leap years. Is there any way to ensure this is done
> > by
> > a formula?
> > PLEASE NOTE: We use the date formula in Australia 31/03/2002.
> >
>
>
>

Hi

The formula returns always the last day of month. In general:
=DATE(YearNum,MonthNum+1,0)
(0th day of month is the last day of previous month in Excel) The formula
returns last day (date) of month MonthNum in year YearNum.

The year number in this formula will be the year number of date in cell A2 +
x, where for A3 x=1, for A4 x=2 , etc.
In my example x = ROW()-2, for A3 ROW()=3, for A4 ROW()=4, etc. - substract
2, and you get what you need.

MonthNum=MONTH($A$2)



--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )



"Jenny Potter" <(E-Mail Removed)> wrote in message
news:A6670F22-4FAD-4FDD-A277-(E-Mail Removed)...
> Thanks so much for the quick response - what does it all mean? I'd like to
> be
> able to understand it.
> ie: +ROW()-2
> ie: MONTH($A$2)
> ie: +1,0
> Regards
>
> "Arvi Laanemets" wrote:
>
>> Hi
>>
>> When you enter start date into A2, then into A3 enter the formula
>> =DATE(YEAR($A$2)+ROW()-2,MONTH($A$2)+1,0)
>> , and format in any valid date format.
>> Copy A3 down.
>>
>> --
>> Arvi Laanemets
>> ( My real mail address: arvi.laanemets<at>tarkon.ee )
>>
>>
>>
>> "Jenny Potter" <(E-Mail Removed)> wrote in message
>> news:B0EFD3C4-EA95-48B8-8414-(E-Mail Removed)...
>> >I have a spreadsheet where in one column (Column A) a user needs to
>> >insert
>> >a
>> > year end date (eg: 30/06/2002) as the starting date of the loan. Then
>> > the
>> > years increment below - Each cell below this starting cell needs to
>> > increase
>> > yearly for the term of the loan.
>> > So I need calculations for if there is a leap year, etc.
>> > I have used =A10+365 but it doesn't calculate for leap years so I have
>> > to
>> > insert the 366 on the leap years. Is there any way to ensure this is
>> > done
>> > by
>> > a formula?
>> > PLEASE NOTE: We use the date formula in Australia 31/03/2002.
>> >
>>
>>
>>