Solved

Returning A Value If Cell Date Is Between Two Dates

Posted on 2013-02-07
2
371 Views
Last Modified: 2013-02-07
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.
0
Comment
Question by:edreamers
2 Comments
 
LVL 23

Accepted Solution

by:
NBVC earned 500 total points
ID: 38865078
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
 
LVL 26

Expert Comment

by:redmondb
ID: 38865145
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now