+ Reply to Thread
Results 1 to 5 of 5

Conditional Formatting and Dates

  1. #1
    Registered User
    Join Date
    10-17-2019
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    3

    Conditional Formatting and Dates

    Hi,

    I have a training spreadsheet where I record the dates of passing tests/assessments, some of these assessments have a timelapse period.

    User enters dates into the date column field (red cell) and in a subsequent column (green cell) the amount of months until this assessment is required again.

    I'd like to conditionally format the column of dates (E,F etc) so that the individual cells turn red after the amount of months are up (W)

    I do not want to add another column with the a run out date as multiple employees will be listed in the sheet and it'll just make it more complex.

    What formula do I use in conditional formatting please?

    skills regsiter.jpg

    Many thanks

    Paul

  2. #2
    Registered User
    Join Date
    08-07-2019
    Location
    Scotland
    MS-Off Ver
    2016
    Posts
    56

    Re: Conditional Formatting and Dates

    Hey Paul,

    the below should work for you, just copy the formatting down. Would be the way i would do it

    =IF(EDATE(E5,W5)>TODAY(),0,1)

  3. #3
    Registered User
    Join Date
    10-17-2019
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    3

    Re: Conditional Formatting and Dates

    Quote Originally Posted by GordonKy42 View Post
    Hey Paul,

    the below should work for you, just copy the formatting down. Would be the way i would do it

    =IF(EDATE(E5,W5)>TODAY(),0,1)
    Thanks Gordon, unfortunately it didn't work in conditional formatting for me. No matter what date I enter, the cell remains red, indicating its overdue.

  4. #4
    Registered User
    Join Date
    10-17-2019
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    3

    Re: Conditional Formatting and Dates

    Quote Originally Posted by Chadd157 View Post
    Thanks Gordon, unfortunately it didn't work in conditional formatting for me. No matter what date I enter, the cell remains red, indicating its overdue.
    Sorry Gordon, let me confirm, it does work but defaults to the end of the month that its looking at, so although it will work for what I need it to do, the formula doesn't specially work if I was to count on the actual day.

    Many thanks for your help on this.
    Paul

  5. #5
    Registered User
    Join Date
    08-07-2019
    Location
    Scotland
    MS-Off Ver
    2016
    Posts
    56

    Re: Conditional Formatting and Dates

    Hey,

    Not sure why see attached made a test sheet and seems to work. ignore column X that was just so i can see the date returned.
    If you attach a copy of your sheet can have a look, i may be misunderstanding the way you want it set up
    Attached Files Attached Files

+ 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. [SOLVED] Multi-Conditional Conditional Formatting and Dates
    By Mousiefuzz in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-07-2015, 04:09 AM
  2. Conditional formatting using dates and using dates without years
    By dcef79 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-15-2014, 05:35 AM
  3. Replies: 11
    Last Post: 05-04-2014, 08:28 PM
  4. [SOLVED] Conditional Formatting Due dates and Completed Dates
    By shansen79 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 04-30-2014, 01:33 PM
  5. Conditional Formatting with approaching due dates and completed dates
    By rogernation in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-13-2013, 04:12 PM
  6. Replies: 4
    Last Post: 08-29-2013, 11:23 AM
  7. [SOLVED] Conditional Formatting of: dates within 1 month and dates before today.
    By Luke Smith in forum Excel General
    Replies: 5
    Last Post: 06-18-2013, 07:29 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1