+ Reply to Thread
Results 1 to 7 of 7

Conditional Formatting Training Matrix

  1. #1
    Registered User
    Join Date
    11-10-2011
    Location
    Waterford, Ireland
    MS-Off Ver
    Excel 2007
    Posts
    7

    Conditional Formatting Training Matrix

    Hi All,

    I am sure someone can help me here, i have tried to use conditional formatting to highlight the colour of a cell in my Training Matrix, either by Green good for 11 months, Yellow for the last month and Red for anything over the time period.

    On the Matrix, it will have various expiry dates from 1 year to 4 years depending on the training that was carried out


    Training Matrix Register-page-001.jpg

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Conditional Formatting Training Matrix

    Not enough info, can you provide examples of dates and what you're expecting to see?
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Registered User
    Join Date
    11-10-2011
    Location
    Waterford, Ireland
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Conditional Formatting Training Matrix

    Each row will have a different expiry dated,

    For instance Company, Induction is 1-year expiry from date completed that is starting in columns C5,

    First Aid is 2 years expiry from date completed is in columns F5

    Is it possible to have different expiry dates for each columns

  4. #4
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Conditional Formatting Training Matrix

    You'll have to adjust the formulas replacing 12 with a different number of months depending on the column's expiry.
    You havent mentioned what cell the date is in, I'm assuming B1, adjust as necessary.

    Select the column you want to highlight

    Conditional Formatting
    New Rule
    Use a formula to determine...

    3 formulas required , one for each colour

    =AND(C3 < > 0,C3 < EDATE($B$1,12))
    format as green

    =AND(C3 < > 0,DATEDIF(MIN(C3,$B$1),MAX(C3,$B$1),"m")=1)
    format as yellow

    =AND(C3 < > 0,C3 <=EDATE($B$1,12))
    format as red

    Repeat for the other columns changing the value 12 in the formulas depending when the expiry date is, e.g. for 4 years expiry 12 should be 48 (number of months).


    NOTE: I had trouble with the yellow formula, test thoroughly with different dates

  5. #5
    Registered User
    Join Date
    11-10-2011
    Location
    Waterford, Ireland
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Conditional Formatting Training Matrix

    Thanks for your reply, not getting the fomula to input and error comes up maybe i wrote and copied it in same error came up.

    The only one i changed was C3 to C6 where the cell is, date is in B1, I was unable to upload the excel sheet, so i can only show a screenshot

    =AND(C6 < > 0,C6 < EDATE($B$1,12))
    format as green

    =AND(C6 < > 0,DATEDIF(MIN(C6,$B$1),MAX(C3,$B$1),"m")=1)
    format as yellow

    =AND(C6 < > 0,C6 <=EDATE($B$1,12))
    format as red

    Training Matrix.jpg

  6. #6
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Conditional Formatting Training Matrix

    See below (post appeared twice hence this message)...
    Last edited by Special-K; 01-16-2019 at 07:59 AM.

  7. #7
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Conditional Formatting Training Matrix

    Youll need to remove the spaces from the formulas as this forum gets confused with < > characters and assumes it is html code.

    Attaching files:

    Use the Go Advanced option at the bottom of the page then scroll down to Manage Attachments as the "paperclip" method does not work on this forum.

+ 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: 1
    Last Post: 09-19-2018, 07:07 PM
  2. Training Matrix - Conditional Formatting for dates occuring
    By Smannarn in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-25-2016, 11:37 AM
  3. Replies: 1
    Last Post: 10-09-2014, 08:29 AM
  4. [SOLVED] Conditional Formatting for Training Expiration Dates
    By jeff.kennedy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-29-2014, 11:58 AM
  5. Training matrix traffic lights conditional formatting
    By buju247 in forum Excel General
    Replies: 6
    Last Post: 11-29-2013, 05:44 AM
  6. Replies: 3
    Last Post: 09-20-2013, 02:04 AM
  7. Conditional Formatting Training Matrix Dates
    By hamiltg02 in forum Excel General
    Replies: 2
    Last Post: 05-01-2013, 05:49 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