Trying to do a date range IF statement

[markman235]

Hi everyone,

I'm stuck on what looks like a pretty simple formula. What I am trying to do is, I have a work schedule for a production team on one tab. I also have a vacation listing on a second tab.

I am trying to write a statement that will say to look at the employee's name (A6), look at date for that day (C4), and look at the vacation tab and to see if that person's name is listed and if so, is that days date within the vacation date range. (The vacation start date is Vacations!B:B, and the end date is in Vacations!C:C)


=IF(AND(MATCH(A6,Vacations!A:A,0),C4>=Vacations!B:B,C4<=Vacations!C:C),"Vacation","Off")

Thanks,

Mark

 

[markman235]

Update:

I found a solution by getting a bit creative and removing one of the match criteria and adding an IF/CountIf statement to get the first condition met. It might be a little sloppy, but it works for what I need. And as a point of clarification, the "off" at the end is just because this is that individual's day off. Other cells have "5:30A - 2:00P".

Here is the formula: =IFERROR(IF(COUNTIF(Vacations!$A$2:$A$50,$A$6)>0,INDEX(Vacations!$E$2:$E$49,MATCH(1,IF(Vacations!$B$2:$B$49<=C4,IF(Vacations!$C$2:$C$49>=C4,1)),0)),"Off"),"Off")

I hope this helps!

Mark