# Simply cannot find an answer

Solved/Closed
mona3 Posts 5 Registration date Thursday January 24, 2013 Status Member Last seen January 25, 2013 - Jan 24, 2013 at 07:18 PM
mona3 Posts 5 Registration date Thursday January 24, 2013 Status Member Last seen January 25, 2013 - Jan 25, 2013 at 12:04 AM
Hello,

I have a spread sheet - column B lists dates for the last time a report for each product was completed. Each report is due every 28 days.

This is what I want:

Dates in column B to change to:

Red when the next report is due today or overdue
Orange when the next report is due within the next 7 days
White when the report is not due for at least 8 or more days

I can make it work if the dates are those for when the next report is due but I can't get it to work when the dates of the last report are in the column.

Hope that makes sense.

Related:

## 2 responses

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jan 24, 2013 at 11:10 PM
use this for conditional formula

condtion 1 (white)
=AND(A1<>"",TODAY()-A1<21)

condtion 2 (red)
=AND(A1<>"",TODAY()-A1>=27)

condtion 3 (orange)
=AND(A1<>"",TODAY()-A1>=7)
mona3 Posts 5 Registration date Thursday January 24, 2013 Status Member Last seen January 25, 2013
Jan 24, 2013 at 11:18 PM
Wow - that's amazing! Would love to know how on earth you work that out! Obviously I have a long way to go!

Maybe you will be able to help me with my other question...

I have dates in column D and then in the next column I have put the following formular to automatically calculate the number of days since that date. =TODAY()-D2

I then did CTRL + SHIFT + the down arrow, then CTRL + D to fill the entire column with the formula. However, I don't know how to make it not show up a number if there is no date in adjacent cell in column D.

Thanks in advance AGAIN if you can help - greatly appreciate it and very envious of you :)
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jan 24, 2013 at 11:22 PM
use if statement,
=if(d2="","",TODAY()-D2 )
mona3 Posts 5 Registration date Thursday January 24, 2013 Status Member Last seen January 25, 2013
Jan 24, 2013 at 11:23 PM
Where exactly would I put that statement?
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jan 24, 2013 at 11:29 PM
Am i missing some thing here. it seems to me that you were using a formula that was working for you. You wanted on change in it that if D column is empty that formula should not show any thing

So
=TODAY()-D2

becomes
=IF (D2="", "", TODAY()-D2)
meaning if d2 is blank, then show blank, other wise show the result of TODAY()-D2
mona3 Posts 5 Registration date Thursday January 24, 2013 Status Member Last seen January 25, 2013
Jan 24, 2013 at 11:38 PM
It's not working :(
I keep getting an error message - if I click 'ok' then it just puts a 0 in the cell.
It says: Circular reference warning.
One or more formulas contain a circular reference and may not calculate correctly. Circular references are any references within a formula that depend upon the results of that same formula. For example, a cell that refers to its own value or another cell which depends on the original cell's value both contain circular references.
For more information about understanding, finding and removing circular references click help. If you want to create a circular reference click OK to continue.

Any idea what I'm doing wrong?

I have now changed it so that column F has the date and column G should have the number of days since that date (I tried your formula before making this change though).
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jan 24, 2013 at 11:56 PM
you do understand what "circular reference" mean right ? it is saying that formula is directly or indirectly refering to the cell where you are trying to put the formula

let say
if i write in A1
= A1 * 2
thats a circular reference