calculating a cell location for a scheduling spread sheet

[hutch]

I have a scheduling spread sheet that is just about functions the way I
want.
Except...
I have two adjacent cells, one for a start date and one for an end date.
I have formated 5 cells for each week to the right
I would like to be able to have the user enter a start date and an end date
and have excel format the appropriate cells (in the same row) with a gray
25% fill color indicating graphically the date span between the two dates.
I am supposing that I would use Conditional formating and more specifically
"Formula Is" and then test to see if the start date and end date cells falls
between the two dates. If so, then the cell would format. I do have one
cell for the duration using the NETWORKDAYS function where I would show the
quantity of work days ("5 days").

Like this (hope you can see this if the formating in the post come across
correctly.)
In the example below the space between the 22nd and the 25th is a 4 day
span, so the cells would format with gray fill color. In my example the
continuous '|=====|' represents the fill color.
In this example day1 might be 3/21 so the formating would be from day2
through day5.

Start End day1 day2 day3 day4 day5 day6
date date
3/22 3/25 |===============|

Hope this makes sense?

Thanks in advance!

 

[Bernie Deitrick]

Hutch,

Your problem is a little confused: it is unclear how the Day1.... Day6
correspond to anything.

Anyway, perhaps this will help you:

In cell A2, enter the start date. In cell B2, enter the end date. In cells
C1:I1, put the numbers 1 to 7 (representing the days of the week Sunday to
Saturday).

Then select C2:I2, and choose Format | Conditional Formatting....

Choose Formula Is... (instead of "Cell Value is") and use the formula

=AND(C1>=WEEKDAY($A$2),C1<=WEEKDAY($B$2))

and select your gray shading.

HTH,
Bernie
MS Excel MVP