+ Reply to Thread
Results 1 to 10 of 10

Highlight Cells between dates

  1. #1
    Registered User
    Join Date
    11-10-2020
    Location
    Wales
    MS-Off Ver
    Excel 2020
    Posts
    4

    Post Highlight Cells between dates

    Hi all,

    First post so may have been solved somewhere else.

    I have a spreadsheet. Due to technical ICT reasons, we're not allowed to use VBA in it.

    We send out a consultation spreadsheet which has a 14 day turnaround. Other people populate their necessary cells.

    I have a reporting tab front cover and would like to do the following on this tab:

    1 cell that shows 1 of 3 criteria:
    • Green if entry to a cell completed within the 14 days
    • Yellow, which is the time between the initial date and the consultee deadline. Kind of a holding
    • Red if no entry during the consultation period (14 days).

    I can do it a series of logical test, but i really just want something simple that has a colour flag, as per the attached screenshot

    Help!

    excelhelp.PNG

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,578

    Re: Highlight Cells between dates

    Welcome to the forum

    Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    2010
    Posts
    2,500

    Re: Highlight Cells between dates

    if you can do logical tests, you just turn your logical test into a conditional format. It the test is true colour red. If the next test is true colour yellow etc

    However I would echo the above comments without us knowing the results you require, its hard to give an answer. We also don't know the underlying dates (how they are formatted or even if they are text), which cells you want the formulas in or even the cell references as row and column headings are missing

  4. #4
    Registered User
    Join Date
    11-10-2020
    Location
    Wales
    MS-Off Ver
    Excel 2020
    Posts
    4

    Re: Highlight Cells between dates

    I cant upload as its a sensitive government workbook. Im confused of how to do it with 3 logical tests.

  5. #5
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,578

    Re: Highlight Cells between dates

    Just a sample worksheet: "Make sure there is just enough data to demonstrate your need. "

  6. #6
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    2010
    Posts
    2,500

    Re: Highlight Cells between dates

    We don't need the original worksheet, you could upload what is a screen shot just as values on a single sheet, explain in English the logic as to what colour they should be

    how can we possibly know that that yes, no, 0 should not be red in the first line of the table, but the same values are red in the second line?

  7. #7
    Registered User
    Join Date
    11-10-2020
    Location
    Wales
    MS-Off Ver
    Excel 2020
    Posts
    4

    Re: Highlight Cells between dates

    Okay, will try and explain a bit clearer.

    Attachment 703436

    So, with 14 days to respond.

    The received date is the date its sent out to consultees and their deadline is the cell below. E3 and E4 Respectively.

    In the Cell G9, I want it to show if the value for E9 is within the deadline, by way of the 3 colours on the right hand side.

    So if there is a response (as indicated in E9) before E4, the cell in G9 turns green.
    If E4 has not been passed yet and Cell E9 is No (before the deadline) the cell stays orange.
    If E9 is No and this is after the deadine, the cell, G9 turns red.

    hopefully thats a bit clearer.

  8. #8
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    2010
    Posts
    2,500

    Re: Highlight Cells between dates

    Your attachment is an invalid link and not attached as per the instructions Bebo gave you or as in the yellow banner. As a result the below is all guesses

    and($e$4<=today(),E9="no") conditional format yellow

    and($e$4>today(),E9="no") conditional format red


    have the cells as a default colour of green. it will always turn overdue cells green one you type yes, which you may or may not want

  9. #9
    Registered User
    Join Date
    11-10-2020
    Location
    Wales
    MS-Off Ver
    Excel 2020
    Posts
    4

    Re: Highlight Cells between dates

    Thanks, ive tried the manage attachments way?
    Attached Images Attached Images

  10. #10
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    2010
    Posts
    2,500

    Re: Highlight Cells between dates

    You haven't as a spreadsheet and still not explained why row 9 and 10 do not both have the same format. Does my previous answer do what you want, and if not why?

+ 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] Want to highlight cells with expiry dates
    By Tony0731 in forum Excel General
    Replies: 3
    Last Post: 05-29-2018, 03:55 AM
  2. Highlight cells between 2 dates
    By mikehk in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 10-05-2017, 12:20 AM
  3. USing VBA to highlight cells between dates
    By cthamas222 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-16-2016, 08:25 AM
  4. [SOLVED] How do I highlight cells that do not contain valid dates.
    By AndyRoo119 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-09-2015, 09:04 AM
  5. [SOLVED] Highlight cells between two dates
    By YasserKhalil in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-25-2015, 05:05 AM
  6. [SOLVED] Help with Macro to highlight cells between dates
    By Slomaro2000 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-27-2015, 03:29 PM
  7. [SOLVED] Highlight Cells Based on Dates
    By pugsly8422 in forum Excel General
    Replies: 3
    Last Post: 01-27-2015, 01:09 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