# VLOOKUP with Date Question

#### Mark Carver

##### New Member
I am working on a generic mortgage worksheet that I will include in other real estate-related worksheets. However, I've hit a wall with a particular formula. Basically, I want to figure out how to determine how many years and months remain on a loan. I have attached my spreadsheet with the problem notated in cell L2. THANKS for your help!

#### Attachments

• 56.8 KB Views: 6

#### GraH - Guido

##### Well-Known Member
Perhaps this can help.
[O13]=DATEDIF(O11,EDATE(E11,12*E9),"Y")&" Yr(s) "&DATEDIF(O11,EDATE(E11,12*E9),"YM")&" Mth(s)"

#### Attachments

• 56.8 KB Views: 6

#### Mark Carver

##### New Member
Thanks. I will study this and give it a try.

#### Mark Carver

##### New Member
Guido, THANKS, you're a genius!
- Quick help with modification, if you will. On the attached spreadsheet where I inserted your formula, it works as long as the loan term is 30 years. However, if I shorten it to less than 30 years, say 15, I get VALUE#. The relevant cells are J12 (your formula), E9 (Loan term), and E12 (nearest today's date). I will eventually hide E12 by making the font white.
- Not directly related, but is there a way to conditional format the table to only the length of months dictated by the loan term?

#### Attachments

• 63.5 KB Views: 1

#### GraH - Guido

##### Well-Known Member
Hi Mark,
The #VALUE! is caused by your formula in E12, which is an input for the formula in J11. You could use
[E12]=AGGREGATE(146,(\$C\$17:\$C\$376)/(\$C\$17:\$C\$376<TODAY()),1)
Hiding can be done by the custom format ";;;" by the way. But better is having such a formula stored in the name manager (F3, look on the ribbon on formula section). Both ways are in the file.
If using the name manager, there is no need for the formula in E12 at all.

=AND(\$B18<>"",MOD(ROW(),2)=0), deleted the outer grid borders.

Glad to have been of assistance.

#### Attachments

• 41 KB Views: 6

#### Mark Carver

##### New Member
THANK YOU. You are a Godsend!