Relative References in Conditional Formatting (bars & scales)

I am an Equipment Operator with the Navy Seabees, and the Yard Boss at Camp Moreell in Kuwait. We have CESE (Civil Engineering Support Equipment) going into IEM (Inactive Equipment Management). What this means; our CESE has to be cycled (checked for preventative maintenence issues, started up, ran...) once a week if it hasn't been used within a seven day period, but CESE going into IEM only needs to be cycled once per month.

Column E in my report reflects that as a two part eight day graph, with the formula =TODAY()-8 for the shortest end of the bar graph and the red of the graded color scale, and =TODAY() for the long end of the bar and the green of the color scale. If a piece is cycled today I enter today's date and the bar shows full and the scale is green. As that date approaches 8 days past, the bar moves to the left and the scale changes yellow to red.

I need that graph (column E) to change to a thirty day scale when I change the LOCATION (column D) to IEM, back to an eight day if I change it to DISPATCH, and to clear out completely if I change to any other location.

I've tried a few different things, but can't seem to get any of them to work. One thing I tried that I thought would work--but didn't--is to make column H (which I planned to hide) equal eight if the corresponding row in column D equals DISPATCH (using an IF formula), and to equal thirty if it's IEM. Then I wrote my graph formula a few different ways to the general effect of =TODAY()-H:H but no way that I wrote it worked. I couldn't figure out a way to just make DISPATCH equal a hidden value of eight, and IEM to equal thirty, but since the formula I did for the method I tried didn't work anyway, I wouldn't have gotten that to work either. I was able to get column H to reflect eight or thirty in accordance with column D, but I can't get the graph to cooperate.

I also tried entering the formula =IF(D:D="IEM",TODAY()-31,TODAY()-8) into the Conditional Formatting formula field for the color scale and data bar, but I get the error "You cannot use relative references in Conditional Formatting criteria for color scales, data bars, and icon sets."

What's my work around?

That's pretty good, pb71, not exactly the effect I was going for, but since I seem to have encountered a limitation in Excel, this might just be my only option. It's exactly what I'm looking for, except I liked having the date on top rather than in a seperate column. I had a feeling some tricky math would lead to my fix.

You get an add for that. Thanks!

what would I add to that formula to get "NA" in the graph column (F) if there is no entry in the date column (E)?

Thanks pb71, you've been a great help. I've used your formula and have gotten the spreadsheet working up to my standards. I now only have one remaining issue. First let me clearify that I cannot use macros on the military LAN and computers because they're blocked, and there's no acceptions for this (I've asked). So a macro solution is out.

My problem is, if I hide the formula and protect the sheet, when I add a new row the formula is not copied into the new row. My users need to be able to add new rows when new CESE comes in. If I add a new row, and drag the formula down from the row above, it only copies the format, not the formula. Is there a way to get this hidden formula ot copy into a new row?