Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Automatic shading of cells based on dates???
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 |
#2
|
|||
|
|||
Automatic shading of cells based on dates???
Pedros,
Select all of the cells you want shaded, starting at D2 across and down, Use conditional formatting, FormatConditional 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" wrote in message ... 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 |
#3
|
|||
|
|||
Automatic shading of cells based on dates???
Not enough information to give a specific answer, but in general,
conditional formatting will do what you want. "Pedros" wrote in message ... 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 |
#4
|
|||
|
|||
Automatic shading of cells based on dates???
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. -- Pedros ------------------------------------------------------------------------ Pedros's Profile: http://www.excelforum.com/member.php...o&userid=28202 View this thread: http://www.excelforum.com/showthread...hreadid=477432 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Apply cell shading based on adjacent cells in EXCEL | Excel Worksheet Functions | |||
Automatically filling in cells based on another cell's content | Excel Worksheet Functions | |||
moving cells based certain value | New Users to Excel | |||
Auto populate cells based on 2 cells division. | Excel Discussion (Misc queries) | |||
Calculate percentage based on cells with conditional formatting | New Users to Excel |