A bit of a Stumper
 

Hi Folks,

this is my first post here, could anyone help me out a bit, basically
what i am trying to do is to use excel to calculate maximum mortgage
term available to someone, for example, i want to input the persons
date of birth (23/07/75) and i want it to calculate the maximum term
they can have a mortgage for up to the maximum age 67. Do i make any
sense here. Whether the response comes through for excel or excel vb i
am willing to try anything.

Many Thanks

Lee Elliot
:confused:


--
cdu311
------------------------------------------------------------------------
cdu311's Profile: http://www.excelforum.com/member.php...o&userid=27051
View this thread: http://www.excelforum.com/showthread...hreadid=468258

I put 1/18/1945 in B18, and =TODAY() in C18 and =67-(YEAR(C18)-YEAR(B18)) in
another cell and got 7.00 (with the right formatting)
--
Gary''s Student


"cdu311" wrote:


Hi Folks,

this is my first post here, could anyone help me out a bit, basically
what i am trying to do is to use excel to calculate maximum mortgage
term available to someone, for example, i want to input the persons
date of birth (23/07/75) and i want it to calculate the maximum term
they can have a mortgage for up to the maximum age 67. Do i make any
sense here. Whether the response comes through for excel or excel vb i
am willing to try anything.

Many Thanks

Lee Elliot
:confused:


--
cdu311
------------------------------------------------------------------------
cdu311's Profile: http://www.excelforum.com/member.php...o&userid=27051
View this thread: http://www.excelforum.com/showthread...hreadid=468258

This doen't quite work. It takes no account of the time of year. If you
change the birthdate to be later in the year that today, you should expect
the resultant number of years to change.

A1 =TODAY()
A2 is birthdate
A3 =DATE(YEAR(A2)+67,MONTH(A2),DAY(A2))
Year calculation
=YEAR(A3)-YEAR(A1)-IF(OR(MONTH(A3)<MONTH(A1),AND(MONTH(A3)=MONTH(A1), DAY(A3)<DAY(A1))),1,0)

--
Ian
--
"Gary''s Student" wrote in message
...
I put 1/18/1945 in B18, and =TODAY() in C18 and =67-(YEAR(C18)-YEAR(B18))
in
another cell and got 7.00 (with the right formatting)
--
Gary''s Student


"cdu311" wrote:


Hi Folks,

this is my first post here, could anyone help me out a bit, basically
what i am trying to do is to use excel to calculate maximum mortgage
term available to someone, for example, i want to input the persons
date of birth (23/07/75) and i want it to calculate the maximum term
they can have a mortgage for up to the maximum age 67. Do i make any
sense here. Whether the response comes through for excel or excel vb i
am willing to try anything.

Many Thanks

Lee Elliot
:confused:


--
cdu311
------------------------------------------------------------------------
cdu311's Profile:
http://www.excelforum.com/member.php...o&userid=27051
View this thread:
http://www.excelforum.com/showthread...hreadid=468258

Thanks for that - it works lovley :cool: but i would also like to
include months, erm - sorry to be a pain in the neck. if i type in (for
example) 12/08/1980 it comes back with 39 years but i need to show
months as well because it would actually be 39 years 11 months based on
65 year finish (41 years 11 months based on 67 year finish).

Much appreciated - you rock Ian!!!

Thanks

Lee

:rolleyes:


--
cdu311
------------------------------------------------------------------------
cdu311's Profile: http://www.excelforum.com/member.php...o&userid=27051
View this thread: http://www.excelforum.com/showthread...hreadid=468258

try the datedif() function
(not covered in help unless you have 2000)
=datedif(birthdate.today(),"y") for years
=datedif(birthdate.today(),"ym") for monthssince last bithday
=datedif(birthdate.today(),"md") for if you are also interested in days
You can see more info at

http://www.cpearson.com/excel/datedif.htm

"cdu311" wrote:


Thanks for that - it works lovley :cool: but i would also like to
include months, erm - sorry to be a pain in the neck. if i type in (for
example) 12/08/1980 it comes back with 39 years but i need to show
months as well because it would actually be 39 years 11 months based on
65 year finish (41 years 11 months based on 67 year finish).

Much appreciated - you rock Ian!!!

Thanks

Lee

:rolleyes:


--
cdu311
------------------------------------------------------------------------
cdu311's Profile: http://www.excelforum.com/member.php...o&userid=27051
View this thread: http://www.excelforum.com/showthread...hreadid=468258

:cool: IT WORKS. Many Thanks guys for your help

Much appreciated

Lee

;)


--
cdu311
------------------------------------------------------------------------
cdu311's Profile: http://www.excelforum.com/member.php...o&userid=27051
View this thread: http://www.excelforum.com/showthread...hreadid=468258