+ Reply to Thread
Results 1 to 17 of 17

Conditional Formatting for 2 months from today

  1. #1
    Registered User
    Join Date
    08-22-2017
    Location
    calilfornia
    MS-Off Ver
    2010
    Posts
    9

    Conditional Formatting for 2 months from today

    Hi all, looking for some help with creating a conditional formatting formula for dates between 30-60 months, basically for anything 2 months from today's date.
    I've googled and came up with this =AND($H2-TODAY()>=30, $H2-TODAY()<=60) but I can't get it to work as a conditional formatting rule. I've changed the cells' formatting to reflect the colors I want, so it's not like I'm forgetting that piece.
    This is what I'm trying to do. In the image below, I want the dates in October to be highlighted. And then when it's September, I want the months in November to be highlight, etc. Any help would be greatly appreciated!
    dates.png

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,018

    Re: Conditional Formatting for 2 months from today

    What happens when you use that as a CF rule (New Rule/Use Formula)?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    8,550

    Re: Conditional Formatting for 2 months from today

    Try
    =TEXT($H2,"MMMYY")=TEXT(EDATE(TODAY(),2),"MMMYY")
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  4. #4
    Registered User
    Join Date
    08-22-2017
    Location
    calilfornia
    MS-Off Ver
    2010
    Posts
    9

    Re: Conditional Formatting for 2 months from today

    Quote Originally Posted by nflsales View Post
    Try
    =TEXT($H2,"MMMYY")=TEXT(EDATE(TODAY(),2),"MMMYY")
    When I try that, nothing happens still. I'm still learning excel but am I suppose to change something or just copy and paste as is? (I just copied and pasted as is into a new CF rule using the "use a formula" rule type and nothing worked). I'm missing something.
    Last edited by drunkchicken; 08-22-2017 at 02:43 AM.

  5. #5
    Registered User
    Join Date
    08-22-2017
    Location
    calilfornia
    MS-Off Ver
    2010
    Posts
    9

    Re: Conditional Formatting for 2 months from today

    Quote Originally Posted by FDibbins View Post
    What happens when you use that as a CF rule (New Rule/Use Formula)?
    Nothing happens. I must be missing something.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,194

    Re: Conditional Formatting for 2 months from today

    Will you please attach a sample Excel workbook?

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,018

    Re: Conditional Formatting for 2 months from today

    Did you highlight the range you want to apply this to?

  8. #8
    Registered User
    Join Date
    08-22-2017
    Location
    calilfornia
    MS-Off Ver
    2010
    Posts
    9

    Re: Conditional Formatting for 2 months from today

    I've included the excel as an attachment. I deleted personal information but left the dates.

    So grateful for this forum.
    Attached Files Attached Files
    Last edited by AliGW; 08-22-2017 at 02:50 AM. Reason: Unnecessary quotation removed.

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,194

    Re: Conditional Formatting for 2 months from today

    It's working fine as far as I can see. The rules you have set do not use formulae.

    CF Dates AliGW.png

  10. #10
    Registered User
    Join Date
    08-22-2017
    Location
    calilfornia
    MS-Off Ver
    2010
    Posts
    9

    Re: Conditional Formatting for 2 months from today

    Quote Originally Posted by FDibbins View Post
    Did you highlight the range you want to apply this to?
    Yes, I do/did. I double check in the Rules Manager under "Applies To" but it still doesn't work for me. Again, I know I must be missing some vital step.

  11. #11
    Registered User
    Join Date
    08-22-2017
    Location
    calilfornia
    MS-Off Ver
    2010
    Posts
    9

    Re: Conditional Formatting for 2 months from today

    Quote Originally Posted by AliGW View Post
    It's working fine as far as I can see. The rules you have set do not use formulae.
    So I just used the excel programs CF for dates for this month and next month. I'd like to have the excel document highlight dates 2 months from today. But I can't get the formula to work. Ahh ... since the formula that I originally posted didn't work for me, I took it out of the attached excel doc! Should I have left that in there?

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,194

    Re: Conditional Formatting for 2 months from today

    Try this for your green rule:

    =H2>=EOMONTH(TODAY(),1)+1

    Does it do what you want?

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,194

    Re: Conditional Formatting for 2 months from today

    Any good for you?

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,194

    Re: Conditional Formatting for 2 months from today

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  15. #15
    Registered User
    Join Date
    08-22-2017
    Location
    calilfornia
    MS-Off Ver
    2010
    Posts
    9

    Re: Conditional Formatting for 2 months from today

    Maybe it'll be easier if I reupload the excel document with the CF formula inside. Can anyone hep me figure out where I'm going wrong with that rule? Again, I want dates two months from today to be a certain color (in this case yellow. But as you will see in the excel document, lots of dates turn yellow, even ones that are two years from now). I already have the dates from this month and next month a certain color because that's already built into excel. Thank you!
    Attached Files Attached Files

  16. #16
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B 2016
    Posts
    1,540

    Re: Conditional Formatting for 2 months from today

    If you want the CF to apply to column I as well, remove the "$" in front of "H": =AND(H2-TODAY()>=30, H2-TODAY()<=60)

  17. #17
    Registered User
    Join Date
    08-22-2017
    Location
    calilfornia
    MS-Off Ver
    2010
    Posts
    9

    Re: Conditional Formatting for 2 months from today

    Quote Originally Posted by josephteh View Post
    If you want the CF to apply to column I as well, remove the "$" in front of "H": =AND(H2-TODAY()>=30, H2-TODAY()<=60)
    Thank you so much! You've solved my problem! It appears to be working now!

+ 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. Conditional formatting for Today and Tomorrow
    By alipezu in forum Excel General
    Replies: 1
    Last Post: 01-24-2017, 02:03 AM
  2. [SOLVED] Conditional Formatting for TODAY
    By tapsmiled in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 02-25-2014, 09:18 PM
  3. Conditional Formatting help with =Today()
    By Prosatinos in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-21-2013, 06:56 AM
  4. Replies: 6
    Last Post: 04-23-2012, 11:11 AM
  5. Conditional Formatting Today and Older
    By ConsbruckR in forum Excel General
    Replies: 7
    Last Post: 03-18-2009, 04:31 PM
  6. Conditional Formatting Cell < Today()
    By sagan in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-21-2005, 11:05 AM
  7. [SOLVED] Conditional Formatting ... Today() & More
    By Ken in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-09-2005, 11:06 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