See the top rated post in this thread. Click here

Results 1 to 6 of 6

Thread: Excel Conditional Formatting

  1. #1
    Mod / Salary / SM Wanderer's Avatar
    Join Date
    2001-12
    Location
    St. Louis
    Posts
    5,406
    Login to Give a bone
    0

    Default 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?
    Melanie Stone
    @MistresDorkness

    Archibus, FMS/FMInteract and AutoCAD Expert (I use BricsCAD, Revit, Tandem, and Planon, too)
    Technical Editor
    not all those who wander are lost

  2. #2
    Active Member Pontoon's Avatar
    Join Date
    2006-01
    Location
    2nd star to the right & straight on till Morning
    Posts
    98
    Login to Give a bone
    2

    Default Re: Excel Conditional Formatting

    Yes you can.

    (yearfrac(A1,B1))<>1

    Should work I think
    Last edited by Pontoon; 2014-08-01 at 03:00 PM. Reason: rethinking

  3. #3
    All AUGI, all the time CADastrophe's Avatar
    Join Date
    2012-01
    Location
    Site of the next CADaclysm
    Posts
    813
    Login to Give a bone
    0

    Default 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.
    Last edited by CADastrophe; 2014-08-01 at 03:27 PM.

  4. #4
    Mod / Salary / SM Wanderer's Avatar
    Join Date
    2001-12
    Location
    St. Louis
    Posts
    5,406
    Login to Give a bone
    0

    Default Re: Excel Conditional Formatting

    Quote Originally Posted by Pontoon View Post
    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
    Melanie Stone
    @MistresDorkness

    Archibus, FMS/FMInteract and AutoCAD Expert (I use BricsCAD, Revit, Tandem, and Planon, too)
    Technical Editor
    not all those who wander are lost

  5. #5
    All AUGI, all the time CADastrophe's Avatar
    Join Date
    2012-01
    Location
    Site of the next CADaclysm
    Posts
    813
    Login to Give a bone
    2

    Default Re: Excel Conditional Formatting

    Quote Originally Posted by Wanderer View Post
    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.

  6. #6
    Mod / Salary / SM Wanderer's Avatar
    Join Date
    2001-12
    Location
    St. Louis
    Posts
    5,406
    Login to Give a bone
    0

    Default Re: Excel Conditional Formatting

    Quote Originally Posted by CADastrophe View Post
    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.
    Melanie Stone
    @MistresDorkness

    Archibus, FMS/FMInteract and AutoCAD Expert (I use BricsCAD, Revit, Tandem, and Planon, too)
    Technical Editor
    not all those who wander are lost

Similar Threads

  1. schedules - conditional formatting
    By Wish List System in forum Revit Architecture - Wish List
    Replies: 0
    Last Post: 2014-10-24, 11:28 AM
  2. 2013: Conditional text formatting
    By bjrochford958510 in forum AutoCAD Annotation
    Replies: 1
    Last Post: 2013-04-15, 01:29 PM
  3. Excel Table Conditional Formatting
    By kwest in forum AutoCAD Tables
    Replies: 3
    Last Post: 2009-11-18, 06:28 PM
  4. WISH: Conditional Formatting
    By TroyGates in forum Revit Architecture - General
    Replies: 0
    Last Post: 2008-06-26, 04:44 PM
  5. Conditional Formatting - Schedules
    By Steve_Stafford in forum Revit MEP - General
    Replies: 2
    Last Post: 2006-11-05, 04:55 AM

Posting Permissions

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