Checking if a date falls between 2 (or more) seperate ranges

**ncalvelo** · 01-03-2013, 12:39 PM

Hello,

I'm trying to create a sheet that will let me know if someone has booked leave for the last working day of the week.

Basically when ever someone books leave in our system, the start and end date of their booked time is logged for each leave request creating multiple rows for the same person. For example, If Joe put in 3 different requests, it would show the following in the data export:

A B C

NAME START FINISH
Joe 14/12/2012 14/12/2012
Joe 21/12/2012 27/12/2012
Joe 31/12/2012 04/01/2013

Say I want to know if Joe will be present on each of the days 14th, 21th and 28th of December (each date check to have it's own cell), How would I do this? I have gotten far enough to do the check if I specify the ranges myself, but I'm having trouble getting the formula to use the ranges where Joe is shown in column A to make it less of a chore.

I have attached a spreadsheet which I hope better defines my question. I have excel 2010

Many thanks

ncalvelo

**ChemistB** · 01-03-2013, 12:45 PM

One way, In F4 dragged across and down

=IF(SUMPRODUCT(--($E4=$A$4:$A$8),--(F$3>=$B$4:$B$8),--(F$3<=$C$4:$C$8)),"Absent", "Present")
Does that work for you?

Other option
=IF(COUNTIFS($A$4:$A$8,$E4,$B$4:$B$8,"<="&F$3,$C$4:$C$8,">="&F$3),"Absent", "Present")