Gantt type chart using start & end dates with conditional formatting

Hi

I'm trying to produce a Gantt type chart in Excel 2010 to show resourcing over a financial year. (I know they should really be using a project management tool, but they want to use Excel ). I am almost there but have run into difficulties where the end date is mid-month, and also when a task starts and ends in the same month. I've tried several different formulae, but none of them quite work,.

The formula I am using is as follows:-

Formula:

=IF(AND([@[Start Date]]<'LookUp Table'!$B3,[@[End Date]]>'LookUp Table'!$D2),[@[% time]],"")

Formula:

=AND($A5="Series Management",E5<=1)

Again, obviously the text in inverted commas and formatting changes for each task

I've attached a sample file, does anyone have any ideas where I might be going wrong? I just can't get my head round it at all!