Automatic shading of cells based on dates???

  • Thread starter Thread starter Pedros
  • Start date Start date
P

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?
 
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)
 
Not enough information to give a specific answer, but in general,
conditional formatting will do what you want.
 
Bob, thanks for your help but I cannot seem to get a result with you
suggestion. No formatting is occuring, I don't know what the proble
is.

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

I basically want a page that looks similar to a horizontal bar grap
where the bars that are shaded in represent the period of time that
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 base
on the dates in B1&C1. If I change the dates in B1 and/or C1 I want th
shading to change accordingly.

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