A bit of a Stumper

C

cdu311

Hi Folks,

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

Many Thanks

Lee Elliot
:confused
 
G

Gary''s Student

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)
 
I

Ian

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)
 
C

cdu311

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

Much appreciated - you rock Ian!!!

Thanks

Lee

:rolleyes
 
B

bj

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
 
Top