A bit of a Stumper

[cdu311]

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

[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)
--
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
>
>
>
> --
> cdu311
> ------------------------------------------------------------------------
> cdu311's Profile: http://www.excelforum.com/member.php...o&userid=27051
> View this thread: http://www.excelforum.com/showthread...hreadid=468258
>
>

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

--
Ian
--
"Gary''s Student" <[email protected]> wrote in message
news:[email protected]...
>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
>>
>>
>>
>> --
>> cdu311
>> ------------------------------------------------------------------------
>> cdu311's Profile:
>> http://www.excelforum.com/member.php...o&userid=27051
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=468258
>>
>>

[cdu311]

Thanks for that - it works lovley 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

[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

"cdu311" wrote:

>
> Thanks for that - it works lovley 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
>
>
>
>
> --
> cdu311
> ------------------------------------------------------------------------
> cdu311's Profile: http://www.excelforum.com/member.php...o&userid=27051
> View this thread: http://www.excelforum.com/showthread...hreadid=468258
>
>

[cdu311]

IT WORKS. Many Thanks guys for your help

Much appreciated

Lee