+ Reply to Thread
Results 1 to 4 of 4

Conditional Formatting - Comparing date columns

  1. #1
    Registered User
    Join Date
    10-08-2015
    Location
    Auckland
    MS-Off Ver
    Excel 2010
    Posts
    2

    Conditional Formatting - Comparing date columns

    Hi,

    I would like to compare two columns of dates (G & H) and highlight the ones where G is NOT 7 days ahead of H. So far I've tried different variations using DATEDIF function, but haven't had much luck.

    I don't care which cell, or if both cells, are highlighted.

    Help/Tips/Solutions are appreciated!

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Conditional Formatting - Comparing date columns

    Hi, welcome to the forum

    Assuming you know how to add a new rule (use formula) in CF, your formula needs to be something like...

    =G2<H2+7
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    10-08-2015
    Location
    Auckland
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Conditional Formatting - Comparing date columns

    Quote Originally Posted by FDibbins View Post
    Hi, welcome to the forum

    Assuming you know how to add a new rule (use formula) in CF, your formula needs to be something like...

    =G2<H2+7
    I did not think simple arithmetic would work on date fields in excel... still, the result is not consistent...

    xcl.jpg

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Conditional Formatting - Comparing date columns

    What you need to understand about dates and times in excel is...

    a date is just a number representing the number of days passed since 1/1/900...and then formated in a way that we recognise as a date. So, for instance, today (Fri Oct 2015) is actually 42286

    Time is actually a decimal part of 1 (day), so 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 6 PM) is 0.75

    Upload a sample workbook please, not a pic

+ 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. Conditional Formatting based on comparing the dates in 2 columns
    By ghostface in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-11-2014, 05:54 PM
  2. [SOLVED] Comparing values in three columns with conditional formatting
    By Jason Grunert in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-19-2013, 11:59 AM
  3. Replies: 8
    Last Post: 11-02-2011, 07:08 PM
  4. Conditional Formatting Comparing Values in Two Columns
    By meyero90 in forum Excel General
    Replies: 5
    Last Post: 08-16-2010, 07:56 AM
  5. comparing columns and conditional formatting icons
    By Galoredk in forum Excel General
    Replies: 3
    Last Post: 02-19-2010, 12:00 PM
  6. Conditional Formatting-Comparing Two Columns
    By Tom K in forum Excel General
    Replies: 3
    Last Post: 05-08-2006, 08:03 AM
  7. Conditional Formatting-Comparing Two Columns
    By Tom K in forum Excel General
    Replies: 1
    Last Post: 05-04-2006, 01:25 PM

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