software

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

quot;Arvi Laanemetsquot; wrote:

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

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