I would like to create a calendar worksheet which updates from a data sheet containing a start date and end date.

The task I have been given is as follows: There is an excel worksheet which contains a list of jobs. In this table each job has a field for who or what the asset is, where it has been sent, a start date and an end date. I have been asked to create a second calendar sheet which will shade a calendar based on which workers/assets are out on each day, and show where they are. Of particular difficuly to me, is the fact that this would need to be inclusive of the date range. i.e cover all dates contained between the start date and the end date. Also the jobs come in thick and fast, not every worker/asset would need to be represented on the calendar only the 7 most critical. Also the length of the job could be for any duration. Due to the way jobs are entered they are not necessaraly sequential.

I have attached an example of what I am trying to get to. The approach I have taken so far is to use various IF functions to identify if the calendar date is => start date and =<the end date. But basically I havent got anywhere with this. I spent an unproductive day working (googling) this problem and have been unable to solve it.

I am hoping some very cleaver person may be able to come up with a solution. Many thanks

Calendar Example.xls


I am using Excel 2003 on Windows XP