# Returning A Value If Cell Date Is Between Two Dates

i have a problem i want to calculate dearness allowance of our employees which is associated with dates,

we have a dearness rate of 0% from 01-01-2006 to 30-06-2006,
2% from 01-07-2006 to 31/12/2006,
4% from 01-01-2007 to 30/06/2007,
6% from 01-07-2007 to 31/12/2007,
9% from 01/01/2008 to 30/06/2008,
12% from 01/07/2008 to 31/12/2008,
16% from 01/01/2009 to 30/06/2009,
22% from 01/07/2009 to 30/06/2009

when we type date in a1, it will automatically write the associted dearness rate in b1.

Thanx.
###### Who is Participating?

x

Commented:
If you have the start dates say in X2:X9, and the end dates in Y2:Y9, along with corresponding rates in Z2:Z9,

Then try:

=SUMIFS(\$Z\$2:\$Z\$9,\$X\$2:\$X\$9,"<="&A1,\$Y\$2:\$Y\$9,">="&A1)

adjust ranges to suit where you want the data to be.
0

Commented:
Hi,  edreamers.

Please see attached. (I've assumed that your last date should be 31/12/2009).

The formula is...
=IF(OR(A1<G1,A1>=G9,NOT(ISNUMBER(A1))),"",INDEX(\$F\$1:\$F\$9,MATCH(A1,G1:G9,1),0))

Let me know if you'd like a message (rather than blank) for invalid input.

Regards,
Brian.Rates.xls
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.