Solved

Returning A Value If Cell Date Is Between Two Dates

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
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 demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

747 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

14 Experts available now in Live!

Get 1:1 Help Now