Date Formula - in Years

[Guest]

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.

 

[Arvi Laanemets]

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.

 

[Guest]

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]

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)