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.
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.
I'm trying it. I want to setup something like this
If C1 date is within the current year I want it to highlight the year. This returns an error though.
Please Login or Register to view this content.
I'm guessing if I want to highlight paste years it would be something like
Please Login or Register to view this content.
is this in google docs - or excel ?
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.
try three custom formula rules
=J1<today()-730
=AND(J1<today()-365,J1>=today()-730)
=J1>=today()-365
sorry you have changed the rules
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
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.
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*
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
That doesn't seem to work when I tested it. I'm using the conditional formatting on column A if you want to check.Please Login or Register to view this content.
OK
should be just
=Year($J1)=year(today())
let me try
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
Great! Thank etaf. That worked perfectly. I realized my mistake right after I posted that.
Sharing has been turned off.
excellent , your welcome and thanks for the rep
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks