Excel Date Lookup Problem

Hi:

I am trying to figure out a formula(s) or table setup where I can take a date from a column (1/1/02) and compare it to the date in another column (10/15/05) and calculate the number of days. I then want to look up a set of rates for the corresponding year.

As an example.
xdate=10/15/04
ydate=10/15/05
days = 365 so go to table 1 for rates

xdate=10/15/00
ydate=10/15/05
days=1825 go to tabel 5

any ideas?

Thanks,
Pat

 

I tend to brute-force formulas since Excel is pretty fast. I'm positive there are more elegant ways to do what you want but this will do what I think you are asking for.

First (and taken from Excel Help) set to allow cell labels to be used in formulas. This option must be set when you first create a workbook, or you will need to re-enter the formulas that use labels.
- Click Options on the Tools menu, and then click the Calculation tab.
- Under Workbook options, select the Accept labels in formulas check box.
- To re-enter a formula that used a label before setting this option, select the cell that contains the formula, press F2, and then press ENTER.

Set the date cells as data type Date. Set the formula cell as Number.

Assuming you put the older dates in col A and the newer ones in Col B then in Col C you could calculate days by a simple forumla
=B1-A1 and get a number representing the difference in days.

In Col D you could then use a forumla like
=IF(C1>1825,table5,IF(C1>1460,table4,IF(C1>1095,table3,IF(C1>730,table2,table1))))

If you wanted to eliminate the calculation in Col C, you could simply change the If,then,else formula by substituting (B1-A1) for C1.

 

pat396 - did the above formula do it for you?