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

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

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

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?

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.

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

Thanks, ive tried the manage attachments way?

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?