Monday, April 18, 2011

yogi_Automatically Highlight Dates That Fall Within A Specific Month

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
kanting said:
Hi there - I am rather desperately looking for help on this issue, so thank you in advance for your suggestions!
I have a spreadsheet used to plan the editorial content of my school newspaper. We get a lot of information months in advance, and have typically just thrown it onto our google doc. But we've noticed that we've started to miss a lot of stories, so I've come up with a way to add an "alert" to rows that are taking place within the month.
In column A of my spreadsheet, I have used =IF( AND( C3>=TODAY() ; C3<=EDATE(TODAY();1)) ; 1;0) to give the value 1 if the date in column C is within a month of today. Then I have added a Changed color with Rule to have a yellow marker come up when A is equal 1. This isn't ideal, but it works. The problem I have is that if I insert a row, I have to drag the formula down to the new cell in order to get the marker. This is extremely inconvenient when you have a lot of people editing a document. Is there a way to get the function to appear automatically? Or, better yet, is there another way I could automatically highlight cells in a column that are in the upcoming month?

---------------------------------------------------------------------------------------

In Google spreadsheet, as of this time Change color with rules (conditional formatting) one can not use formulas for conditional formatting of the cells ... so I used formula in column A to comute whether dates in cells C2:C are in the month = current month + value in cell A1 ... and then I used the Change color with rules formatting to change the Background color of the cells to Yellow if the value in corresponding cell of column A is TRUE. I formatted the TEXT and BACKGROUND color of cells in A2:A to be YELLOW.