formula needed to work with conditional format for homemade

winrz

Board Regular
Joined
Sep 4, 2002
Messages
106
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..
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

zzydhf

Board Regular
Joined
Apr 10, 2002
Messages
236
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....
 

Watch MrExcel Video

Forum statistics

Threads
1,122,554
Messages
5,596,814
Members
414,104
Latest member
imamalidadashzada

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top