edreamers
asked on
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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(IS
Let me know if you'd like a message (rather than blank) for invalid input.
Regards,
Brian.Rates.xls