+ Reply to Thread
Results 1 to 7 of 7

Date entered in cell to automatically change colour, when expiry is close or passed.

  1. 04-21-2015, 09:01 AM #1
    maccacan
    maccacan is offline
    Registered User
    Join Date
    05-19-2012
    Location
    Grimsby
    MS-Off Ver
    Excel 2010
    Posts
    4

    Date entered in cell to automatically change colour, when expiry is close or passed.

    Hi,

    It would be greatly appreciated if somebody could help me!
    I'm relatively new to Excel....

    The problem is that I'm devising a training matrix for our school.
    What i'm trying to establish is, when a person has undertaken some training, i'd like to enter the date they completed the training into a cell.
    Then i'd ideally like the cell colours to change automatically by conditionally formatting them and not have to manually fill the cells with the relevant colour.

    The colours i would like the cell to be are:
    Green - Valid
    Amber - Re-training due within 2 months
    Red - Expired

    I've tried various things but i'm finding it difficult to do!


    Please see the example attached.

    Many thanks in advance!

    Training Spreadsheet.xlsx
    Reply With QuoteRegister To Reply
  2. 04-21-2015, 10:52 AM #2
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Date entered in cell to automatically change colour, when expiry is close or passed.

    1) Remove all other Conditional Formatting and paint the cells green as their normal background color. Or paint them white and create a CF that turn them green if the cell has a number in it greater than zero.

    2) Create the following two CF formulas:

    RED: =DATEDIF(D3,TODAY(),"m")>=$A3
    ORANGE: =DATEDIF(D3,TODAY(),"m")>=$A3-2
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)
    Reply With QuoteRegister To Reply
  3. 04-21-2015, 02:22 PM #3
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Date entered in cell to automatically change colour, when expiry is close or passed.

    My apologies, one other change... take the word "months" out of the column A cells, just put the number of months in those cells as whole numbers only.
    Reply With QuoteRegister To Reply
  4. 04-22-2015, 03:40 AM #4
    maccacan
    maccacan is offline
    Registered User
    Join Date
    05-19-2012
    Location
    Grimsby
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Date entered in cell to automatically change colour, when expiry is close or passed.

    Perfect! Many thanks.
    Reply With QuoteRegister To Reply
  5. 04-23-2015, 11:18 AM #5
    maccacan
    maccacan is offline
    Registered User
    Join Date
    05-19-2012
    Location
    Grimsby
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Date entered in cell to automatically change colour, when expiry is close or passed.

    Sorry to be a pain, is there a way to copy these formulas over a range of cells?
    Reply With QuoteRegister To Reply
  6. 04-23-2015, 01:16 PM #6
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Date entered in cell to automatically change colour, when expiry is close or passed.

    COPY > PASTE SPECIAL > Validation
    Reply With QuoteRegister To Reply
  7. 04-24-2015, 03:16 AM #7
    maccacan
    maccacan is offline
    Registered User
    Join Date
    05-19-2012
    Location
    Grimsby
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Date entered in cell to automatically change colour, when expiry is close or passed.

    Thanks again. You're a star!!!!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. autocalculate expiry date / expiry date colour warnings
    By kimbling1 in forum Excel General
    Replies: 8
    Last Post: 08-27-2014, 02:53 AM
  2. Change cell colour based on expiry dates
    By lb2013 in forum Excel General
    Replies: 9
    Last Post: 11-15-2013, 01:51 AM
  3. Change colour of cell relative to date. (Passed-Green, Today-Orange Future-Green)
    By lukefallon in forum Excel General
    Replies: 6
    Last Post: 08-23-2012, 12:14 PM
  4. How to change cell colour after a year has passed??
    By jamie.c in forum Excel General
    Replies: 12
    Last Post: 02-16-2009, 04:31 AM
  5. [SOLVED] Change cell Colour when a number of days have been passed
    By SR7133 in forum Excel General
    Replies: 4
    Last Post: 03-01-2006, 06:55 PM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1