Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 -- cdu311 ------------------------------------------------------------------------ cdu311's Profile: http://www.excelforum.com/member.php...o&userid=27051 View this thread: http://www.excelforum.com/showthread...hreadid=468258 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 -- cdu311 ------------------------------------------------------------------------ cdu311's Profile: http://www.excelforum.com/member.php...o&userid=27051 View this thread: http://www.excelforum.com/showthread...hreadid=468258 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Format Stumper | Excel Discussion (Misc queries) | |||
Stumper WORKDAYS Problem | Excel Discussion (Misc queries) |