+ Reply to Thread
Results 1 to 8 of 8

Using conditional formatting to highlight dates with formula (certain criteria)

  1. #1
    Registered User
    Join Date
    11-19-2020
    Location
    South Africa
    MS-Off Ver
    Microsoft Office Home & Business 2016
    Posts
    9

    Question Using conditional formatting to highlight dates with formula (certain criteria)

    Good Day,

    I need help with a formula that will highlight a date meeting certain criteria.

    Spreadsheet example attached.
    In column B we will input a date (let's use 04/11/2020 as an example)
    I need the cell to highlight when the date reaches 7 days or less from 30 days from that date.

    Not sure how to explain it, but using the above example, 30 days from 04/11/2020 will be 04/12/2020, and I need the cell to highlight in red when today's date is 7 days or less from 04/12/2020

    The dates will not all be the same, but I need the formula to calculate the formula without having to input another column that shows the 30 days date.

    Please let me know if anyone can help, an please let me know if this is too confusing. I will try to explain it better

    Thanks Gisela
    Attached Files Attached Files

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Using conditional formatting to highlight dates with formula (certain criteria)

    Select the range B3 to B100 ( say) and use following CF
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Format as required

  3. #3
    Forum Contributor
    Join Date
    05-10-2019
    Location
    Penang, Malaysia
    MS-Off Ver
    2016
    Posts
    262

    Re: Using conditional formatting to highlight dates with formula (certain criteria)

    Hi Gisela1209.

    Let me try to help. I understood you do not wish to create additional column (column C) to show the days to expiry. but this can be hidden & you dont have to do anything since its formularized from the date you've entered. it has a conditional formatting so. if its <=7 days, it will gets highlighted.
    Please find attachment.

    Hope this helps.

    Thank-You very much.
    Attached Files Attached Files

  4. #4
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Using conditional formatting to highlight dates with formula (certain criteria)

    @chaiyya
    No need for extra columns, see my answer

  5. #5
    Registered User
    Join Date
    11-19-2020
    Location
    South Africa
    MS-Off Ver
    Microsoft Office Home & Business 2016
    Posts
    9

    Re: Using conditional formatting to highlight dates with formula (certain criteria)

    Hi Pepe,
    Thanks so much for the help. I added the formula under Conditional Formatting and chose my formatting, but it does not highlight my cell when the date range is as explained in the thread.
    Not sure if I am doing something wrong, but I copied the formula to my clipboard and pasted it.

    What Chaiyya has shown in the sheet with the added columns is essentially what I need, but I want to avoid adding more columns. I would prefer using a formula in conditional formatting to calculate this.
    I have attached the spreadsheet with your formula, could you perhaps please check whether I have done something wrong?
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    11-19-2020
    Location
    South Africa
    MS-Off Ver
    Microsoft Office Home & Business 2016
    Posts
    9

    Re: Using conditional formatting to highlight dates with formula (certain criteria)

    Hi Chiayya, thank you very much for your help.
    Your columns are essentially what I need, but I'd prefer not to add more columns, even if they are hidden.
    Human error is a bigger risk to me on hidden columns than having one column with a formula in the conditional formatting.

  7. #7
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Using conditional formatting to highlight dates with formula (certain criteria)

    There was a typo in my formula try
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    PS don't only Copy/Paste a formula, try to understand what it's doing
    Last edited by Pepe Le Mokko; 12-01-2020 at 09:26 AM.

  8. #8
    Registered User
    Join Date
    11-19-2020
    Location
    South Africa
    MS-Off Ver
    Microsoft Office Home & Business 2016
    Posts
    9

    Re: Using conditional formatting to highlight dates with formula (certain criteria)

    Hi Pepe,

    Thank you, yes I always try to understand the formula cause it helps me to understand other formulas as well.
    I only copy and paste to avoid any typing errors. The formula is working perfectly and it is also working on other date ranges as well when I change the amount of days in which it needs to highlight.
    Thanks a lot for your help.

+ 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 to Highlight Dates in a Column
    By tatertot7 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-22-2019, 03:36 PM
  2. Conditional Formatting to highlight consecutive dates?
    By LisaSchon in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-03-2018, 04:32 AM
  3. [SOLVED] Conditional Formatting formula to highlight a column if multiple criteria are not met.
    By KMJ256388 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-15-2018, 09:36 AM
  4. Need a 'conditional formatting' formula to highlight cells between dates/text
    By BigSean762 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-29-2014, 02:33 PM
  5. [SOLVED] Conditional formatting to highlight old dates?
    By mhaness in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-25-2014, 11:57 AM
  6. Replies: 5
    Last Post: 09-24-2012, 05:14 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