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