Comparing a date with a range of dates

[Hany ElKady]

I have a table as follows

A B C D
Start Date End Date Employee Vacation Type

I then need to create a calendar to how each day in the month and who is
available and who has taken a leave and which type of leave.

So, I need to search the table for each day of the month to find if it is in
the list or not, and if it is, to put the type of leave for THAT person in
the calendar view.

The calendar looks like this...

Date 1/1 1/2 1/3 1/4
Emp1
Emp2
Emp3

 

[T. Valko]

Try this...

The raw data table in the range A15.

Calendar in the range A12:E15

Enter this array formula** in B13:

=INDEX($D$2:$D$5,MATCH(1,($C$2:$C$5=$A13)*($A$2:$A$5<=B$12)*($B$2:$B$5>=B$12),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Copy across to E13 then down to B15:E15.

When the criteria are not met the formula will return #N/A. You can either
include an error trap in the formula to account for these which will make
the formula about twice as long or you can use conditional formatting to
"hide" the #N/A. I would recommend using CF to "hide" them. They'll still be
there, you just won't see them. Or, you can just leave them. Post back and
advise on how you'd like to handle that.

 

[Hany ElKady]

Hi Biff,

Thanks I seem to had been going around in circules with a lot of IF
statements, this is much easier. Just one last thing, the CF, I can't seem to
be able to hide the error #N/A, is there a way to tell it to HIDE the
content, because I had formatted this table using the standard table
formating in excel, and each row is a different color, plus I already have
conditional formatting to show weekends and holidays.

Thanks
Hany

 

[T. Valko]

Well, if you can add a condition to the CF or incorporate this into an
existing condition, what you want to do is test the cell for an error and
set the text color to be the same as the background color thus making the
text (or the #N/A error) unseen.

=ISERROR(A1)

If you can't do that then you'll need to use an error trap in the formula:

=IF(ISNA(MATCH(1,($C$2:$C$5=$A13)*($A$2:$A$5<=B$12)*($B$2:$B$5>=B$12),0)),"",INDEX($D$2:$D$5,MATCH(1,($C$2:$C$5=$A13)*($A$2:$A$5<=B$12)*($B$2:$B$5>=B$12),0)))

 

[Hany ElKady]

Hi Biff, thanks for all your help.

I tried your formula, and then did a bit of tweaking myself... I actually
got a much shoter formula by using iferror instead of isna

Tanks

 

[T. Valko]

using iferror instead of isna

Yeah, one of the advantages of using Excel 2007.

Thanks for the feedback!