Comparing a date with a range of dates

  • Thread starter Thread starter Hany ElKady
  • Start date Start date
H

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
 
Try this...

The raw data table in the range A1:D5.

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.
 
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
 
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)))
 
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
 
using iferror instead of isna :)

Yeah, one of the advantages of using Excel 2007.

Thanks for the feedback!
 
Back
Top