+ Reply to Thread
Results 1 to 8 of 8

Date Specific Conditioning

  1. #1
    Registered User
    Join Date
    10-13-2014
    Location
    Stroud, England
    MS-Off Ver
    Vista
    Posts
    4

    Date Specific Conditioning

    Hello all.

    I have been trying to condition cells using the TODAY() approach.

    My Spreadsheet is required to highlight cells in 3 seperate coluors based on the date entered and the actual date.

    Im trying to condition the cells to achieve the following.
    - If the date entered is 11 months ago or longer i need the cell to go RED.
    - If the date entered is between 8 months and 10 months ago i need i need the cell to go ORANGE
    - If the date entered is less than 8 months ago i need teh cell to be GREEN

    When using the TODAY() function i cant seem to get my head around wether i should be using between, less than, greater than etc.

    Any help would be greatly appriciated.

    Cheers Kev

  2. #2
    Registered User
    Join Date
    10-10-2014
    Location
    India
    MS-Off Ver
    2010
    Posts
    41

    Re: Date Specific Conditioning

    You will have to use 3 conditional formats.
    - RED - Less than or equal to TODAY()-330
    - ORANGE - Between TODAY()-240 and TODAY()-330
    - GREEN - Greater than or equal to TODAY()-240

  3. #3
    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: Date Specific Conditioning

    Slight variation on Vidya's suggestion...

    1. if you will only ever have 3 colors, make 1 the default color for all cells (say GREEN), then you only need 2 CF rules.
    2. for RED =EDATE(TODAY(),-11)>A1
    3. for ORANGE =AND(EDATE(TODAY(),-8)>=A1,EDATE(TODAY(),-11)<=A1)
    4. For GREEN (IF you need 1) =EDATE(TODAY(),-8)<=A1

    Using EDATE() is more accurate than trying to use a set number of days
    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

  4. #4
    Registered User
    Join Date
    10-10-2014
    Location
    India
    MS-Off Ver
    2010
    Posts
    41

    Re: Date Specific Conditioning

    Thanks Ford. Coming across this formula for the first time.
    Vidya Palani

  5. #5
    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: Date Specific Conditioning

    Vidya, thanks for the feedback, that can be a very useful function to use, it can add or subtract months from a date

  6. #6
    Registered User
    Join Date
    10-13-2014
    Location
    Stroud, England
    MS-Off Ver
    Vista
    Posts
    4

    Re: Date Specific Conditioning

    Ok thanks both for your replys , i cant seem to get FDibbins to work , my worksheet stays blank ?

    I am entering the formulae as below:-

    Rule Type:
    Format only cells that contain:
    Cell Value: Equal too: ="EDATE(TODAY(),-11)>A1" (RED)
    Cell Value: Equal too: ="AND(EDATE(TODAY(),-8)>=A1" (ORANGE)
    Cell Value: Equal too: ="EDATE(TODAY(),-8)<=A1" (GREEN)

    My date format im using is 14/10/14.

    Any ideas ?

    Cheers Kev

  7. #7
    Registered User
    Join Date
    10-13-2014
    Location
    Stroud, England
    MS-Off Ver
    Vista
    Posts
    4

    Re: Date Specific Conditioning

    Its ok guys i have cracked it......

    Use a formulae to determine whihc cells to format......

    Funny it does exactly as it says

    Cheers for your help

  8. #8
    Registered User
    Join Date
    10-13-2014
    Location
    Stroud, England
    MS-Off Ver
    Vista
    Posts
    4

    Re: Date Specific Conditioning

    One more question if i wanted all the blanks to be white , what would be the best way to do this ?

+ 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: 2
    Last Post: 02-18-2014, 12:22 PM
  2. Conditioning? Curent date and time
    By bvanscoy678 in forum Excel General
    Replies: 7
    Last Post: 08-05-2010, 02:19 PM
  3. Automatically send an email from excel with specific data on specific date.
    By Angela1607 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-04-2009, 08:11 AM
  4. [SOLVED] conditioning
    By DONNA in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-07-2005, 12:35 PM
  5. Replies: 1
    Last Post: 11-01-2005, 06:05 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