return a date from range, date is between dates in two other cells

[Guest]

I have a range of dates, S2:AF2. I need to search within that range to
locate any that are between the dates in two other cells, AG2 and AH2. If
none are between the dates, I wish the calculated cell to remain blank.

 

[Dave Peterson]

A formula like:
=COUNTIF(S2:AF2,">"&AG2) - COUNTIF(S2:AF2,">="&AH2)
(AG2 holds the earlier date. AH2 holds the later date.)



So you could do:
=if((COUNTIF(S2:AF2,">"&AG2) - COUNTIF(S2:AF2,">="&AH2))=0,"",yourformulahere)

if you don't know which date will be earlier or later, you could use:
=COUNTIF(S2:AF2,">"&min(AG2,ah2)) - COUNTIF(S2:AF2,">="&max(ag2,AH2))

And I'm not sure what should happen at the cusps--when the dates are equal to
the AG2 and AH2. You may have to fiddle with the inequalities.