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" <JennyPotter@discussions.microsoft.com> wrote in message
news:A6670F22-4FAD-4FDD-A277-96529A47645E@microsoft.com...
> 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" <JennyPotter@discussions.microsoft.com> wrote in message
>> news:B0EFD3C4-EA95-48B8-8414-0A5A6E6C8E98@microsoft.com...
>> >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.
>> >
>>
>>
>>