Thursday, April 19, 2012

yogi_Work Around For Conditionally Formatting Dates In A Column Based on A Formula

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #512    Apr 19, 2012     www.energyefficientbuild.com.

user GavinP said:
Google Spreadsheet - Conditional Formatting Question
I have a spreadsheet containing various values.  One column, column I (i) contains a list of dates. 
I need each date to appear RED if
1) The date is Today (I can do this)
2) The date is any date prior to Today (I can do this)
3) The date is 20 days in the future from Today (I can't do this)
1) and 2) are easy to do. I can not for the life of me work out how to do 3) on Google Spreadsheets.  There is no simple option to click 'If the date is 20 days in the future...'.  
Could someone help me please?  
----------------------------------------------------------------------------------------------
following is a solution to the problem ... since one can not use directly a formula for conditional formatting, I have colored cells of adjacent column J in place of cells in column I


6 comments:

  1. Thank you very much for getting back to me. I'm a little bit confused however and I have been unable to get this working. Would you be able to email me the spreadsheet you have made please? My email address is gavparker@gmail.com. Thank you in advance!

    ReplyDelete
  2. Hi Gavin:

    In response to your request, I have shared the associated spreadsheet with you.

    Cheers!
    Yogi

    ReplyDelete
  3. Hi Yogi. Thanks for getting back to me. It all works very well however I require the cell to turn red if the date is any time between today and 20 days in the future.

    For example, today is 24/04/2012, so I need the cell to be red if the date is between 25/04/2012 and 14/05/2012 (20 days in the future). The spreadsheet is designed to warn me if a date has either passed or is coming up 20 days in the future. Thanks!

    ReplyDelete
  4. Hi Gavin:

    I have posted a solution for your requirements stated in your last comment in my following blog post ...
    http://yogi--anand-consulting.blogspot.com/2012/04/yogiwork-around-for-conditionally_24.html

    Cheers!
    Yogi
    Cloud Computing -- Google Docs Way
    yogi--anand-consulting.blogspot.com

    ReplyDelete
  5. It was an excellent effort made by you through your nice piece of writing, holding the quality and knowledge together for the readers. consulting

    ReplyDelete
  6. You Are Very Welcome Mike ... Now Let Us Keep Googling.

    Cheers!
    Yogi
    Cloud Computing -- Google Docs Way
    yogi--anand-consulting.blogspot.com

    ReplyDelete