+ Reply to Thread
Results 1 to 8 of 8

changing the colour of a cell if the date in the cell is expiring

  1. #1
    Registered User
    Join Date
    09-30-2020
    Location
    Inverness, Scotland
    MS-Off Ver
    2010
    Posts
    4

    changing the colour of a cell if the date in the cell is expiring

    I am creating a personnel certificate matrix where i am entering individual dates when a certificate was attained for each person.

    I have spent 2 days trying to find a way to do this but after trying so many different ways i haven't found a solution yet.

    So what i am trying to achieve is that depending on the duration that the certificate lasts for example - 2 years or 3 years or 5 years i am trying to use conditional formatting to highlight the cell containing the date one month before it expires?

    If i cant use the same cell then maybe i could use a pivot table on a separate sheet and enter the interval for each certificate?

    I will be using this formula for each certificate column

    If anyone can help it would extremely helpful

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    12,988

    Re: changing the colour of a cell if the date in the cell is expiring

    We need to see a sample of how you have things set up. See yellow banner at top of page.
    There should be a column with your certificate lengths and one with your date which will change color.
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    09-14-2020
    Location
    Valdosta, GA
    MS-Off Ver
    Office 2019
    Posts
    18

    Re: changing the colour of a cell if the date in the cell is expiring

    Good day.

    Please see attached. I just had this issue in regards to cert expiration dates.

    I used Conditional formatting to get colors to display cells after a certain amount of days.

    See if this will help.

    Nvable

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    12,988

    Re: changing the colour of a cell if the date in the cell is expiring

    Is this what you are trying to do? Since you already have a column which determines whether the certificate is valid or about to expire, I just referenced that column (Col E)
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    09-30-2020
    Location
    Inverness, Scotland
    MS-Off Ver
    2010
    Posts
    4

    Re: changing the colour of a cell if the date in the cell is expiring

    Hi there and thank you very much for your swift reply.

    It is basically what i am trying to do but i was trying to enter a formula in the same cells of a whole column for the date that is entered pertaining to each person in a row.

    Maybe easier way to explain is Column D is for the same certificate that expires in 5 years. Column E is for a certificate that expires in 2 years.
    Personnel are on rows.

    So each persons date of attainment will be different but their certificates still expire after the set interval of the Cert and i wanted to make the cell with their date change colour when they are in a month of expiry.

    Hope that makes sense

  6. #6
    Registered User
    Join Date
    09-30-2020
    Location
    Inverness, Scotland
    MS-Off Ver
    2010
    Posts
    4

    Re: changing the colour of a cell if the date in the cell is expiring

    Hi guys,

    I think i may have over complicated it as it looks like i may have solved it myself with the following:

    I created three conditional formatting rules

    One for blanks

    One to flag up when the five year expiry is up - CELL VALUE <NOW()-1820

    And one to flag up a month in advance - CELL VALUE <NOW()-1795

  7. #7
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    12,988

    Re: changing the colour of a cell if the date in the cell is expiring

    Sounds like you solved it. I would suggest using TODAY() instead of NOW() [NOW() uses Date and Time, and you only need date]. If you could mark this thread "Solved" (go to your first post and click on "Thread Tools."

  8. #8
    Registered User
    Join Date
    09-30-2020
    Location
    Inverness, Scotland
    MS-Off Ver
    2010
    Posts
    4

    Re: changing the colour of a cell if the date in the cell is expiring

    Thank you everyone

+ 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: 3
    Last Post: 03-28-2017, 07:03 AM
  2. Cell to calculate when another cell date is expired or close to expiring
    By Muzza86 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-28-2014, 11:08 AM
  3. Combining codes and changing cell colour using date.
    By kawaitang in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-30-2013, 08:56 AM
  4. Replies: 3
    Last Post: 07-14-2012, 02:36 PM
  5. Replies: 1
    Last Post: 12-08-2011, 09:15 AM
  6. Help changing colour of cell depending on date entered
    By dazlia in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 06-07-2011, 10:59 AM
  7. Changing cell fill colour based on date in cell - Multiple rows
    By ExcelNewb2010 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-26-2010, 07:30 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