Solved

Returning A Value If Cell Date Is Between Two Dates

Posted on 2013-02-07
2
387 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

ScreenConnect 6.0 Free Trial

Discover new time-saving features in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

Question has a verified solution.

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

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associateā€¦
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
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ā€¦
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

832 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