Excel Conditional Formatting
I just started using some conditional formatting, basic stuff.
Basically, if this cell's value reads 'complete', color it blue, if it is marked 'on hold' color it pale green, 'in progress' red, etc. Handy visual stuff.
NOW, I'm hoping one of you gurus out here can tell me whether I can set up a rule across two cells and format based upon the difference between the two?
Say, I've got two columns of dates, and, in order to audit the data, I need to know if those dates are anything other than a year apart.
01/01/2014 - 01/01/2015
wouldn't change formatting because it's a year
but
08/01/2014 - 01/01/2015
would change color because it's not a year difference between the two values.
Make sense? Is this possible?
Re: Excel Conditional Formatting
Yes you can.
(yearfrac(A1,B1))<>1
Should work I think
Re: Excel Conditional Formatting
The formula for the conditional formatting should be something like:
=YEARFRAC(OFFSET(A1,ROW()-1,0),OFFSET(B1,ROW()-1,0))<>1
This is provided that the top line of the data or the header is at ROW 1. If it is somewhere else on the sheet, adjust the "-1" accordingly or use the following formula instead:
=YEARFRAC(OFFSET(A1,ROW()-ROW(███),0),OFFSET(B1,ROW()-ROW(███),0))<>1
For each blank, select the cell just above the first line of data for each column.
Re: Excel Conditional Formatting
Quote:
Originally Posted by
Pontoon
Yes you can.
(yearfrac(A1,B1))<>1
Should work I think
Brilliant, that works.
Of course, I wasn't that familiar with the data until I actually ran this report, though... I'm evidently going to have to be more complicated, dagnabbit.
Looks like the cost start date is going to be more like 2014-06-01 and the end date will be 2015-05-31.
The yearfrac funtion does give me some other tips to try, thanks. Like maybe I can count the days, and use a range, from like 364 to 366 days...
http://spreadsheets.about.com/od/exc...c-function.htm
Re: Excel Conditional Formatting
Quote:
Originally Posted by
Wanderer
Brilliant, that works.
Of course, I wasn't that familiar with the data until I actually ran this report, though... I'm evidently going to have to be more complicated, dagnabbit.
Looks like the cost start date is going to be more like 2014-06-01 and the end date will be 2015-05-31.
The yearfrac funtion does give me some other tips to try, thanks. Like maybe I can count the days, and use a range, from like 364 to 366 days...
http://spreadsheets.about.com/od/exc...c-function.htm
In that case, the formulas below have been modified from my previous post:
=AND(YEARFRAC(OFFSET(A1,ROW()-1,0),OFFSET(B1,ROW()-1,0))>1-(2/365),YEARFRAC(OFFSET(A1,ROW()-1,0),OFFSET(B1,ROW()-1,0))<1)
(assuming the top row of data or the header is on ROW 1)
...otherwise...
=AND(YEARFRAC(OFFSET(A1,ROW()-1,0),OFFSET(B1,ROW()-ROW(███),0))>1-(2/365),YEARFRAC(OFFSET(A1,ROW()-ROW(███),0),OFFSET(B1,ROW()-1,0))<1)
For each blank, select the cell just above the first line of data for each column.
Re: Excel Conditional Formatting
Quote:
Originally Posted by
CADastrophe
In that case, the formulas below have been modified from my previous post:
=AND(YEARFRAC(OFFSET(A1,ROW()-1,0),OFFSET(B1,ROW()-1,0))>1-(2/365),YEARFRAC(OFFSET(A1,ROW()-1,0),OFFSET(B1,ROW()-1,0))<1)
(assuming the top row of data or the header is on ROW 1)
...otherwise...
=AND(YEARFRAC(OFFSET(A1,ROW()-1,0),OFFSET(B1,ROW()-ROW(███),0))>1-(2/365),YEARFRAC(OFFSET(A1,ROW()-ROW(███),0),OFFSET(B1,ROW()-1,0))<1)
For each blank, select the cell just above the first line of data for each column.
Holy cow... lol. I'm still reading the help file here.
This looks great, Corey, Thanks. I think this will work.
