+ Reply to Thread
Results 1 to 13 of 13

Conditional formatting for 30 days, 3 months, 6 months & 1 year

  1. #1
    Registered User
    Join Date
    01-18-2022
    Location
    UK
    MS-Off Ver
    MircoSoft 365 Apps For Enterprise
    Posts
    6

    Conditional formatting for 30 days, 3 months, 6 months & 1 year

    Hi,

    As in the title I'm trying to use conditional formatting for different dates, for example, if a date is 30 days or less in the past, it's green, over 30 days but less than 3 months will be in orange, more than 3 months red and over 6 months dark red or something like that.

    I've attached an example of what I'm working with...

    Look forward to any replies and TIA...!
    Attached Files Attached Files
    Last edited by fred0420; 01-18-2022 at 12:47 PM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,409

    Re: Conditional formatting for 30 days, 3 months, 6 months & 1 year

    Administrative Note:

    Welcome to the forum.

    Members will tailor the solutions they offer to the version of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.
    Ali


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

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    01-18-2022
    Location
    UK
    MS-Off Ver
    MircoSoft 365 Apps For Enterprise
    Posts
    6

    Re: Conditional formatting for 30 days, 3 months, 6 months & 1 year

    Thanks, Ali, I've updated that now, hopefully this will help others to assist.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,409

    Re: Conditional formatting for 30 days, 3 months, 6 months & 1 year

    2112 isn't a version - it's a release number. Do you have MS365? If so, that's what you need.

    Which dates are you wanting to format? Is it column D?

  5. #5
    Registered User
    Join Date
    01-18-2022
    Location
    UK
    MS-Off Ver
    MircoSoft 365 Apps For Enterprise
    Posts
    6

    Re: Conditional formatting for 30 days, 3 months, 6 months & 1 year

    Oh! - Ok, I'll update that now, sorry..!

    Oh yes, that would've been useful information, sorry again! - All that are filled with dates, so D, E & F.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,409

    Re: Conditional formatting for 30 days, 3 months, 6 months & 1 year

    OK. I'm not 100% this is quite what you want (we'll see), but three rukes:

    RED: =D2<=TODAY()-180
    AMBER: =D2<=TODAY()-60
    GREEN: =D2<=TODAY()-30

    The problem with this is that anything under 30 days old will not be formatted. Is this what you want?

    AliGW on MS365 Insider (Windows) 64 bit

    D
    1
    Emailed
    2
    18/12/2021
    3
    19/10/2021
    4
    21/07/2021
    5
    19/10/2021
    6
    08/11/2021
    7
    23/12/2021
    8
    08/11/2021
    9
    08/11/2021
    10
    08/11/2021
    11
    08/11/2021
    12
    08/11/2021
    13
    08/11/2021
    14
    08/11/2021
    15
    08/11/2021
    16
    08/11/2021
    17
    13/12/2021
    18
    08/11/2021
    19
    08/11/2021
    Sheet: List

  7. #7
    Registered User
    Join Date
    01-18-2022
    Location
    UK
    MS-Off Ver
    MircoSoft 365 Apps For Enterprise
    Posts
    6

    Re: Conditional formatting for 30 days, 3 months, 6 months & 1 year

    Hi Ali,

    Thanks so much for your help, that's looks pretty good!

    I would want anything under 30 days as green if possible..? And is everything over 180 days in red using those formulas?

    Thanks for all your help! - I'll have to check back here tomorrow!

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,409

    Re: Conditional formatting for 30 days, 3 months, 6 months & 1 year

    OK - so where does 60 fit in? You won't need it if everything over 30 and under 180 needs to be amber.

    Try these:

    RED: =D2 < TODAY()-180
    AMBER: =D2 < TODAY()-30
    GREEN: =D2 > =TODAY()-30

  9. #9
    Registered User
    Join Date
    01-18-2022
    Location
    UK
    MS-Off Ver
    MircoSoft 365 Apps For Enterprise
    Posts
    6

    Re: Conditional formatting for 30 days, 3 months, 6 months & 1 year

    Morning Ali,

    I've tried using these formulas, its still doesn't seem to work quite as desired.

    There seems to be the issue that cells don't highlight red over 180 days and also cells do not go green for less than 30 days.

    Ideally the setup I'm trying to achieve is:

    One month (or 30 days) OR LESS = Green

    Between 1 month and 6 months (or between 30 days and 180 days) = Amber

    Over 6 months (or 180 days)= Red

    Is this possible? If so can the same formulas be used to do this?

    TIA for all your help and thank you for your help so far...!
    Attached Files Attached Files
    Last edited by fred0420; 01-19-2022 at 05:29 AM.

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,409

    Re: Conditional formatting for 30 days, 3 months, 6 months & 1 year

    Please attach the workbook so that I can troubleshoot what you have done, otherwise it's just guesswork.

  11. #11
    Registered User
    Join Date
    01-18-2022
    Location
    UK
    MS-Off Ver
    MircoSoft 365 Apps For Enterprise
    Posts
    6

    Re: Conditional formatting for 30 days, 3 months, 6 months & 1 year

    Ali,

    I added it to the above post as I couldn't see how to add it here!

    Thanks again for all of your help...!

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,409

    Re: Conditional formatting for 30 days, 3 months, 6 months & 1 year

    You have the rules in the wrong order - they need to go red - amber - green from top to bottom. Shift them around, click APPLY and let me know if this resolves the issue. If not, please be specific about cells that are not doing what you want.
    Last edited by AliGW; 01-19-2022 at 06:00 AM. Reason: Typo fixed

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,409

    Re: Conditional formatting for 30 days, 3 months, 6 months & 1 year

    As you have seen this and now gone, I hope it's resolved your issue. If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.
    Last edited by AliGW; 01-19-2022 at 06:24 AM.

+ 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. Rounding Off Days converted to year, months an days
    By tssooraj in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-08-2021, 08:02 AM
  2. Calculator to calculate year, months and days
    By Stona in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-15-2020, 09:50 AM
  3. [SOLVED] Years Minus Year Months Days
    By AusExcelNewb in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-19-2020, 10:18 PM
  4. Replies: 10
    Last Post: 02-22-2017, 04:51 AM
  5. [SOLVED] VBA Insert Current Month, Year and subsequent months/year for 15 months
    By MichiganWilliams in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-19-2014, 03:49 PM
  6. Trying to track names with days, months, and year(s)
    By casemanagerny in forum Excel General
    Replies: 8
    Last Post: 01-02-2012, 12:38 PM
  7. Days into Months/Year
    By easycapital in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-30-2008, 06:59 PM

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