IF function for scheduling tool with outlook integration

Hi all,

I'm in desperate need of the help of one of you resident genius'. I'm not quite sure how to approach this one.

I'm trying to build a very simple scheduling tool to improve the decision making process behind the booking of meetings in an environment that houses 80+ staff members.

All bookings are made into the calendar, then the calendar info is exported to an excel file. The excel file has the raw calendar data (Sheet 2) and an overview (Sheet 1).

Sheet 1 will look like a matrix. Basically, timeslots and dates will be in rows, resources in columns. In it's most simple form, if the resource is booked a '1' or other value is placed into the corresponding cell on the grid.

So cell C2, may be 01/01/06 8:00am (row), Resource 1 (column)
cell C3 01/01/06 8:15am, Resource 1
cell D2 01/01/06 8:00am. Resource 2...........and so on.

Where A contains the date and B contains the time.

I'm not sure if the right approach is to develop some scripting to perform the calculation on whether the staff member is booked, or use a worksheet function. I'm hoping someone can help me out by either pointing me in the right direction or providing me with a formula that may get me started.

Going back to the raw outlook data in excel (Sheet 2), lets say column A is date, B is start time, C is finish time and D is resource name.

In english....I think it needs to read (for cell C2 on Sheet 1)

If 'Sheet 2, Date Column, Cell' is equal to 'Sheet 1, A2 (Date)
AND
If 'Sheet 1, B2 (Time)' is between 'Sheet 2, Start Time' and 'Sheet 2, Finish Time'
AND
If 'Sheet 2, Resource' = 'Sheet 1, C1 (Resource)'
THEN
C2 = 1
ELSE
C2 = 0

For the life of me, I cannot seem to work my way through this challenge.

I also think there may be a problem with using a cell range in the formula (as it will need to check all the cells in the raw data). I'm thinking without using VBA - I may need another worksheet to help reach the end result but not sure how.

If anyone can offer any pearls of wisdom to overcome this challenge......I would be extremely grateful!

Thank you

Rob