Automatic shading of cells based on dates???

[Pedros]

Hi All,

What I am requesting help on is a little tricky to explain but I will do the best I can and hopefully someone can answer the query:

I work in a field where I am required to monitor the timeliness of construction projects. I am trying to set up a spreadsheet that has Projects listed downwards in column A with a series of dates in the following columns, including start and end dates. At the end of the columns including the descriptions and dates I want a coloured representation of when these construction projects will occur.

So in Row 1 I have:
Work Description, Start Date, End Date, Jul, Aug, Sept, Oct, Nov, Dec.......... ect.

I want the cells which represent months to be shaded according to the start and end dates of each project and I want the shading to change as the dates do.

Is this possible, does it make sense?

[Bob Phillips]

Pedros,

Select all of the cells you want shaded, starting at D2 across and down,

Use conditional formatting, Format>Conditional Formatting
Change Condition 1 to Formula Is
Add a formula of =TEXT($B2,"mmm")=D$1
Click format
Select pattern and choose a colour
OK
Add Condition
Change Condition 1 to Formula Is
Add a formula of =TEXT($C2,"mmm")=D$1
Click format
Select pattern and choose another colour
OK
OK

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Pedros" <[email protected]> wrote in
message news:[email protected]...
>
> Hi All,
>
> What I am requesting help on is a little tricky to explain but I will
> do the best I can and hopefully someone can answer the query:
>
> I work in a field where I am required to monitor the timeliness of
> construction projects. I am trying to set up a spreadsheet that has
> Projects listed downwards in column A with a series of dates in the
> following columns, including start and end dates. At the end of the
> columns including the descriptions and dates I want a coloured
> representation of when these construction projects will occur.
>
> So in Row 1 I have:
> Work Description, Start Date, End Date, Jul, Aug, Sept, Oct, Nov,
> Dec.......... ect.
>
> I want the cells which represent months to be shaded according to the
> start and end dates of each project and I want the shading to change as
> the dates do.
>
> Is this possible, does it make sense?
>
>
> --
> Pedros
> ------------------------------------------------------------------------
> Pedros's Profile:
http://www.excelforum.com/member.php...o&userid=28202
> View this thread: http://www.excelforum.com/showthread...hreadid=477432
>

[Jezebel]

Not enough information to give a specific answer, but in general,
conditional formatting will do what you want.



"Pedros" <[email protected]> wrote in
message news:[email protected]...
>
> Hi All,
>
> What I am requesting help on is a little tricky to explain but I will
> do the best I can and hopefully someone can answer the query:
>
> I work in a field where I am required to monitor the timeliness of
> construction projects. I am trying to set up a spreadsheet that has
> Projects listed downwards in column A with a series of dates in the
> following columns, including start and end dates. At the end of the
> columns including the descriptions and dates I want a coloured
> representation of when these construction projects will occur.
>
> So in Row 1 I have:
> Work Description, Start Date, End Date, Jul, Aug, Sept, Oct, Nov,
> Dec.......... ect.
>
> I want the cells which represent months to be shaded according to the
> start and end dates of each project and I want the shading to change as
> the dates do.
>
> Is this possible, does it make sense?
>
>
> --
> Pedros
> ------------------------------------------------------------------------
> Pedros's Profile:
> http://www.excelforum.com/member.php...o&userid=28202
> View this thread: http://www.excelforum.com/showthread...hreadid=477432
>

[Pedros]

Bob, thanks for your help but I cannot seem to get a result with your suggestion. No formatting is occuring, I don't know what the problem is.

Jezebel, I will try to explain further in the hope that someone can solve this issue for me.

I basically want a page that looks similar to a horizontal bar graph where the bars that are shaded in represent the period of time that a job will run for. For example:

The Spreadsheet is basically as follows:

A1 = Description of the job.
B1 = Start Date.
C1 = End Date.
D1 - AA1 = Months from Jan 2005 - Dec 2006.

If:
B1 = 01/07/2005
&
C1 = 30/06/2006

Then I want Cells J1 through to U1 to all be automatically shaded based on the dates in B1&C1. If I change the dates in B1 and/or C1 I want the shading to change accordingly.

I hope this is enough detail for someone to help me out.