+ Reply to Thread
Results 1 to 4 of 4

Conditional Formatting for Impending Dates Several Months Out

  1. #1
    Registered User
    Join Date
    06-23-2017
    Location
    Rapid City, SD
    MS-Off Ver
    2013
    Posts
    12

    Conditional Formatting for Impending Dates Several Months Out

    I'm looking to apply conditional formatting to a set of dates.

    Basically, we have several projects that have end dates several months out. I'd like the date to highlight one color when it's less than 18 months until the end date from the current date and a different color if it is less than 12 months.

    Is there a way to do this? I'd also like to do this without using days if possible (e.g. 18 months = 540 days).

  2. #2
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily NL, sometimes UK
    MS-Off Ver
    2010
    Posts
    1,618

    Re: Conditional Formatting for Impending Dates Several Months Out

    Use EDATE in your CF formula.

    Select your range of dates (let's say B2:B20).
    Go to Conditional FormattingNew RuleUse a formula to determine which cells to format.
    Enter this formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Use the Format button to choose the highlight you want (font colour, fill colour, etc).
    With the range still selected, repeat with this formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Choose your other highlight.

    Make sure you do them in that order. If you go to Conditional FormattingManage Rules, you should see the <12 months rule at the top.

    The cell reference in the formula should be the top-left cell of your range - Excel will automatically change it for the rest of the range you have selected.
    If you need to expand the range later, do it using Conditional FormattingManage Rules. You'll see the Applies To range for each rule there.

    Hope that helps.
    Regards,
    Aardigspook

    My internet connection can be intermittent, so sometimes I may not reply quickly to questions. Sorry - it's not personal and I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.

  3. #3
    Registered User
    Join Date
    06-23-2017
    Location
    Rapid City, SD
    MS-Off Ver
    2013
    Posts
    12

    Re: Conditional Formatting for Impending Dates Several Months Out

    Quote Originally Posted by Aardigspook View Post
    Use EDATE in your CF formula.

    Select your range of dates (let's say B2:B20).
    Go to Conditional FormattingNew RuleUse a formula to determine which cells to format.
    Enter this formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Use the Format button to choose the highlight you want (font colour, fill colour, etc).
    With the range still selected, repeat with this formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Choose your other highlight.

    Make sure you do them in that order. If you go to Conditional FormattingManage Rules, you should see the <12 months rule at the top.

    The cell reference in the formula should be the top-left cell of your range - Excel will automatically change it for the rest of the range you have selected.
    If you need to expand the range later, do it using Conditional FormattingManage Rules. You'll see the Applies To range for each rule there.

    Hope that helps.
    Worked perfectly! Thank you!

  4. #4
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily NL, sometimes UK
    MS-Off Ver
    2010
    Posts
    1,618

    Re: Conditional Formatting for Impending Dates Several Months Out

    You're welcome. Thanks for the feedback, the rep and marking the thread as Solved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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