Monday, April 18, 2011

yogi_Automatically Highlight Dates That Fall Within A Specific Month

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                  
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.