+ Reply to Thread
Results 1 to 6 of 6

Conditional Formatting - Highlighting dates in relation to dates in previous columns

  1. #1
    Registered User
    Join Date
    11-13-2020
    Location
    Belfast, Northern Ireland
    MS-Off Ver
    Office 16
    Posts
    3

    Conditional Formatting - Highlighting dates in relation to dates in previous columns

    Hi all

    Please bear with me as this is my first EVER post on any forum!!

    I am trying to formulate a "Sickness Trigger" sheet, so that it highlights dates if my staff members hit any of my company's disciplinary sickness triggers. (Triggers are on attached sheet.)

    So if I can explain, all the staff names will be in column A in my sheet, their 1st period of sickness in column B. 2nd Period in C 3rd In D etc.

    For the attached example, I need any date in column B that is less than 90 days since the date in column A to highlight a colour. I then need any date in column c to highlight one colour if its less than 3 months than the previous date AND if it is hitting 3 occasions in a rolling year, and so on. My sheet has 6 columns for dates.

    I have tried using conditional formatting where you can add more than one argument for cells, but my brain wont work!

    It's probably very simple but i cant get my head around it!

    Any help would be very much appreciated!!

    Last edited by JaneJane999; 11-24-2020 at 09:20 AM. Reason: Advised to rename thread

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,731

    Re: Hi Everyone! I am really stumped with this one---can someone please help?

    Welcome to the forum.

    I think you need to change the title of your thread, as it doesn't really explain what you are trying to do. Read Rule 01 from the Forum Rules in the top menu for further guidance.

    Pete

  3. #3
    Registered User
    Join Date
    11-13-2020
    Location
    Belfast, Northern Ireland
    MS-Off Ver
    Office 16
    Posts
    3

    Re: Hi Everyone! I am really stumped with this one---can someone please help?

    Thank you - does that make more sense? Sorry, very new to this. J

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,731

    Re: Conditional Formatting - Highlighting dates in relation to dates in previous columns

    Thanks for changing the title.

    It would also help if you attached a sample Excel workbook for us to play about with. The yellow banner at the top of the screen gives instructions on how to do this.

    Pete

  5. #5
    Registered User
    Join Date
    11-13-2020
    Location
    Belfast, Northern Ireland
    MS-Off Ver
    Office 16
    Posts
    3

    Re: Conditional Formatting - Highlighting dates in relation to dates in previous columns

    Hi again, I've uploaded a copy of my sample worksheet. Sorry for the delay, it was on my work desktop! Again, any help will be greatly appreciated!
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,567

    Re: Conditional Formatting - Highlighting dates in relation to dates in previous columns

    In the attached copy of the file:
    1st trigger is highlighted in yellow using: =AND(B2<>"",B2<=DATE(YEAR(A2),MONTH(A2)+3,DAY(A2)))
    2nd trigger is highlighted in amber:
    For column C the rule is: =AND(C2<>"",C2<=DATE(YEAR(B2),MONTH(B2)+3,DAY(B2)),C2<=DATE(YEAR(A2)+1,MONTH(A2),DAY(A2)))
    For column D the rule is: =AND(D2<>"",D2<=DATE(YEAR(C2),MONTH(C2)+3,DAY(C2)),D2<=DATE(YEAR(A2)+1,MONTH(A2),DAY(A2)))
    Please include more data in order to see an application of the Last trigger which should be applied to column F: =AND(F2<>"",F2<=DATE(YEAR(A2)+2,MONTH(A2),DAY(A2)))
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. Stumped on this one
    By wlhofer in forum Excel General
    Replies: 1
    Last Post: 09-17-2020, 07:04 PM
  2. vba macro
    By Harry A in forum Excel General
    Replies: 3
    Last Post: 04-14-2017, 08:02 AM
  3. Stumped
    By MightyMouse in forum Excel General
    Replies: 3
    Last Post: 04-02-2007, 06:07 PM
  4. [SOLVED] Really Really Stumped!
    By Duncan in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-31-2006, 02:35 AM
  5. I'm Stumped
    By ExcelBob in forum Excel General
    Replies: 8
    Last Post: 05-25-2006, 08:40 AM
  6. I'm Stumped
    By ExcelBob in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-25-2006, 07:15 AM
  7. stumped ??
    By my in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-13-2006, 07:35 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