+ Reply to Thread
Results 1 to 14 of 14

Conditional formatting help in Google Sheets. Change bg color based on date

  1. #1
    Forum Contributor
    Join Date
    10-29-2004
    Posts
    291

    Conditional formatting help in Google Sheets. Change bg color based on date

    In column A of my spreadsheet I have customers name, in column J there is a date when I last did there work. I want to setup a conditional formatting to change the background color of cells found in column A based on the age of the date found in column J. For example, John Smith would display green if his job was within this year, it would display red if it was last year and purple if it was a year and a half ago.

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    7,321

    Re: Conditional formatting help in Google Sheets. Change bg color based on date

    this is in google docs
    if you look at the conditional for formatting - you can add rules and apply rules based on dates

    have you seen and tried this ?

    can you share a document thats does not contain personal info so we can see the same google document
    Wayne

    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Forum Contributor
    Join Date
    10-29-2004
    Posts
    291

    Re: Conditional formatting help in Google Sheets. Change bg color based on date

    I'm trying it. I want to setup something like this

    Please Login or Register  to view this content.
    If C1 date is within the current year I want it to highlight the year. This returns an error though.

    I'm guessing if I want to highlight paste years it would be something like

    Please Login or Register  to view this content.

  4. #4
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    7,321

    Re: Conditional formatting help in Google Sheets. Change bg color based on date

    is this in google docs - or excel ?

  5. #5
    Forum Contributor
    Join Date
    10-29-2004
    Posts
    291

    Re: Conditional formatting help in Google Sheets. Change bg color based on date

    Sorry, it's a Google Sheet. Here's the link

    https://docs.google.com/spreadsheets...it?usp=sharing

    You can see I applied some formatting but I want to do is color code it relative to the current year. I'm trying to create something that would highlight current year cells as green, ast year yellow, 2 years ago purple, 3 years ago orange and then anything 4 years or older would be red. Exact colors aren't important.

  6. #6
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    7,321

    Re: Conditional formatting help in Google Sheets. Change bg color based on date

    try three custom formula rules

    =J1<today()-730

    =AND(J1<today()-365,J1>=today()-730)

    =J1>=today()-365

    sorry you have changed the rules

  7. #7
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    7,321

    Re: Conditional formatting help in Google Sheets. Change bg color based on date

    for same year

    =Year(J1)=year(today())

    for last year
    =Year(J1)=year(today())-1

    for 2 years
    =Year(J1)=year(today())-2

    =Year(J1)=year(today())-3

    =Year(J1)<=year(today())-4

  8. #8
    Forum Contributor
    Join Date
    10-29-2004
    Posts
    291

    Re: Conditional formatting help in Google Sheets. Change bg color based on date

    Thanks but how would I use this to highlight cells in a different column from J. Something like this?

    Please Login or Register  to view this content.

  9. #9
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    7,321

    Re: Conditional formatting help in Google Sheets. Change bg color based on date

    Yes, the $ will fix the cell to test
    and can then apply to more columns - so you can hoghlight a complete row

    the conditional formatting has the cell range you want to apply to
    if more than one column - add a $ infront of the column

    Isn't J the column you are testing the date for ?

    so the J is testing the date and the applies to is the range you want to highlight

    Why the *
    =$J1*

  10. #10
    Forum Contributor
    Join Date
    10-29-2004
    Posts
    291

    Re: Conditional formatting help in Google Sheets. Change bg color based on date

    Not sure why I included the * (I probably picked it up somewhere else erroneously) but yes, I was testing the J column for the date.

    I'm confused as to how to format the formula. Would it be

    Please Login or Register  to view this content.
    That doesn't seem to work when I tested it. I'm using the conditional formatting on column A if you want to check.

  11. #11
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    7,321

    Re: Conditional formatting help in Google Sheets. Change bg color based on date

    OK

    should be just

    =Year($J1)=year(today())

    let me try

  12. #12
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    7,321

    Re: Conditional formatting help in Google Sheets. Change bg color based on date

    yep, that works

    you may want to take off the share as it looks like this is the type of data you do not want public

  13. #13
    Forum Contributor
    Join Date
    10-29-2004
    Posts
    291

    Re: Conditional formatting help in Google Sheets. Change bg color based on date

    Great! Thank etaf. That worked perfectly. I realized my mistake right after I posted that.

    Sharing has been turned off.

  14. #14
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    7,321

    Re: Conditional formatting help in Google Sheets. Change bg color based on date

    excellent , your welcome and thanks for the rep

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 5
    Last Post: 12-07-2013, 02:21 PM
  2. Replies: 10
    Last Post: 06-19-2013, 05:28 PM
  3. [SOLVED] 2010 Conditional Formatting: Cell Color Change, Based on Ratio (1:1)
    By James_D in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-29-2013, 09:02 PM
  4. Replies: 5
    Last Post: 05-10-2012, 10:49 PM
  5. Replies: 6
    Last Post: 03-20-2012, 12:08 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1