formula needed to work with conditional format for homemade

[winrz]

What I have is a rough Gantt chart (think Microsoft Project) with the dates driving the appropriate length of the colored date 'bars'. This is a takeoff of MrExcels problem of the month.

visualize..
7/10/02-9/1/02 XXXXXXXXXXX
7/20/02-9/30/02 XXXXXXXXXXXXXXX
just to get the picture

...FYI ...the conditional format i have on the worksheet to produce green cells to make up the timebars is:
=AND(Q$5>=$F7,Q$5-6<=$G7,ISNUMBER($F7),ISNUMBER($I$5)) ..this prob means nothing without looking at the spreadsheet - so i continue..

say a bar extends from J6 to P6 - what I would like to do is place the # of weeks inside the bar near the end at N6. My formula to find the # of weeks from two dates is:
=ROUNDUP(DAYS360($F8,$G8)/7,0)&" " (the " " at the end serve to bump the result over to the left a bit to make the numbers visible within the tiny cells - its cludge but it works for this purpose).

I have manually pasted this formula in the second to the last cell of each bar so that they look right justified. but since I manually placed them there, any date changes cause the bars to move/expand/contract which changes the placement of the # of weeks. is there a formula i could use that would identify the end of the conditionally formatted bar, jump back two cells and place the # of weeks formula within so i dont have to keep manually placing that formula every time the date changes?

I have tried to download the html maker as requested to show those that are interested - but i have not gotten it to work on my Excel 2002 yet.

IF anyone has any suggestions THEN i would be so appreciative, ELSE i will continue with this self inflicted torture..

 

[zzydhf]

I have done something like this:
Across the header row are the dates (or weeks).
What I did was to put the formula in each cell of the chart and in the formula I added a check like " if date in header row > today()-1 then blank else formula
This showed my value 1 less place than the bar went.

would like to be more specific, but sheet is at work and I am not....