+ Reply to Thread
Results 1 to 2 of 2

Conditional formatting number of days remaining

  1. #1
    Registered User
    Join Date
    08-22-2020
    Location
    Uk
    MS-Off Ver
    Office 365
    Posts
    18

    Conditional formatting number of days remaining

    Hi I am hoping someone can help,

    I have used excel for awhile but for some reason I just cannot get this formula to work correctly.

    In the example work book in H7 a date is typed.

    What I would like excel to do is count the number of days after this date and highlight any that are becoming due.

    E.g.

    If a cell has a date entered - anything up to 300 days after the date = Green

    If a cell has a date entered - anything between 301 - 364 days after this date - Orange

    If a cell has a date entered - anything over 365 days from this date - Red

    I have tried =Today and multiple formulas but I cannot seem to get it correct.

    Basically it is a matrix which will show how long before something expires and when it is overdue from the original date entered.

    Any help would be really appreciated.

    Kind Regards
    Attached Files Attached Files

  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
    80,869

    Re: Conditional formatting number of days remaining

    Are you still using Excel 2016? If not, please update your profile.

    Three CF rules:

    Green: =AND(H7<>"",TODAY()-H7>=365)
    Amber:=AND(H7<>"",TODAY()-H7<365,TODAY()-H7>=301)
    Red: =AND(H7<>"",TODAY()-H7<301)
    Last edited by AliGW; 03-28-2024 at 08:00 AM.
    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.

+ 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] Applying Conditional Formatting Based on Remaining Days in Excel Cell Content
    By Aanhaai in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-27-2023, 11:18 AM
  2. [SOLVED] Help with formula that calculates the days remaining in the month plus a number
    By Paradigmgaming911 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-04-2023, 09:41 PM
  3. [SOLVED] Conditional formatting for dates within certain number of days
    By kcfungus in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-16-2021, 06:37 AM
  4. [SOLVED] Gantt Chart almost complete. Days complete and days remaining Conditional formatting I th
    By markDuffy in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-04-2019, 06:20 AM
  5. Replies: 1
    Last Post: 08-29-2013, 03:17 PM
  6. [SOLVED] Conditional Formatting while calculating number of days...
    By Syrain in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-05-2012, 12:54 PM
  7. Excel-formula required to calculate number of days remaining
    By sahar_sultana in forum Excel General
    Replies: 10
    Last Post: 04-03-2009, 02:41 PM

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