Returning a date from a range if date falls between two other dates

**sdavison** · 06-23-2013, 09:58 AM

Well, I thought I was done with this until the boss adds "Just one more thing". I guess he's stuck watching re-runs of Columbo! I have learned a great deal here at the forum and am now addicted to learning as much as I can about Excel and what it can accomplish. I know this task is possible, maybe even easy if you know which function to use. We are tracking attendance for employees and allow them to take a Perfect Attendance "PA" day for every 6 months they do not call in sick or late. Once they have earned the attendance day, it must be used within the following 6 month period or else they loose it. I have successfully calculated when the attendance day is earned and when it expires, but I would like to show when it was taken. In my attached sample, the first record should be returned as 6/13/13 as that falls within the "earned" and "expires" dates. The second record should be returned as 6/6/13. I've tried nesting a lookup function within a countifs function, but can not seem to get the syntax right, (I may not even be on the right path). My gut tells me I may need to dive into the Index / Match solution, but have yet to master that one too. I could track it manually, but it is less likely to get messed up by those I work with if it's automated. I'm trying not to turn to the forum for everything, but the more I learn, the more self sufficient I can become.

Once again, I thank you good people of the forum for sharing your knowledge and experience.

Scott

**JBeaucaire** · 06-23-2013, 10:03 AM

THis is a trick formula designed to show the LAST cell in a range that matches specific test(s).

In H6: =LOOKUP(2,1/(I6:AK6="pa"), $I$5:$AK$5)

When you copy that cell down, you will #N/A for the rows where the PA has not been taken yet. You could trap that like so:

=IFERROR(LOOKUP(2,1/(I6:AK6="pa"), $I$5:$AK$5), "")

**sdavison** · 06-23-2013, 10:35 AM

Thank you for the quick reply. That formula returns the date of the last instance of the value in the range. However, because it is possible to have two attendance days per year, is it possible to restrict the results between the two dates? As the criteria is based on the last un-excused absence, it could roll over into a new year. Checking the value against the earned and expired dates would resolve the issue. Does that make any sense?

Thanks again for your assistance.

Scott

**sdavison** · 06-23-2013, 04:33 PM

JB,

Thank you once again, I was able to use that to get 75% of the way there. Before I mark the thread as solved, would you be able to point me towards the modification of the formula to return the first cell in a range to meet the criteria? If I can modify it to return the date of the first time the formula hits a "pa" value, I'm there! The final formula is probably unnecessarily long, but it works.

Now, it's time to find some advanced Excel books!

Scott

**JBeaucaire** · 06-23-2013, 06:35 PM

To find the FIRST range in a cell that matches, just use INDEX/MATCH.

=INDEX($I$5:$AK$5, MATCH("pa", $I6:$AK6, 0))

To trap errors:

=IFERROR(INDEX($I$5:$AK$5, MATCH("pa", $I6:$AK6, 0)), "")

**sdavison** · 06-25-2013, 03:59 AM

Alright,

It took a little while while I was learning at the same time as doing, but I have it. I'm sure it will be revised as I simplify the formulas, however, using a combination of lookup, index/match, countifs and nested if statements, I am able to return the correct dates from the data. Many, Many thanks to JB who got me in the right direction and would have taken me much longer without the assistance. I am grateful to those who are willing to share their experience with those who are still learning.

Thanks again to JB and forum members who are willing to give of their time.

Scott