Simply cannot find an answer [Solved/Closed]

Report
Posts
5
Registration date
Thursday January 24, 2013
Status
Member
Last seen
January 25, 2013
-
Posts
5
Registration date
Thursday January 24, 2013
Status
Member
Last seen
January 25, 2013
-
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.

Thanks in advance.

2 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
765
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)
Posts
5
Registration date
Thursday January 24, 2013
Status
Member
Last seen
January 25, 2013

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 :)
Posts
5
Registration date
Thursday January 24, 2013
Status
Member
Last seen
January 25, 2013

Where exactly would I put that statement?
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
765
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
Your formula
=TODAY()-D2

becomes
=IF (D2="", "", TODAY()-D2)
meaning if d2 is blank, then show blank, other wise show the result of TODAY()-D2
Posts
5
Registration date
Thursday January 24, 2013
Status
Member
Last seen
January 25, 2013

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).
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
765
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
Posts
5
Registration date
Thursday January 24, 2013
Status
Member
Last seen
January 25, 2013

I didn't know but now I do - I googled it and read about changing iterations so did that and now it all works perfectly!

You are a legend :)

Thanks again for your help!

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!