read date and highlight if a month passed

Originally Posted by pelachrum

how would you read value from a cell (which is a date) and apply conditional formating based on that value + 30 days?

Select the range required, then Format, Conditional Format, Formula is:

=A1<(NOW()-30)

and set the required pattern colour.

note, A! = the highlighted (selected) cell in your range.

hth
---

thanks,
i've actually come up with =NOW()-G20>30 in the meantime but...
i need it to be dynamic so instead of G20 (which is my cell at this moment) I need it to be =NOW()-[this given date that's entered in this cell]>30

how do I phrase this so tha Excel understands?
in other words how do I make Excel read what's in there now

Originally Posted by pelachrum

thanks,
i've actually come up with =NOW()-G20>30 in the meantime but...
i need it to be dynamic so instead of G20 (which is my cell at this moment) I need it to be =NOW()-[this given date that's entered in this cell]>30

how do I phrase this so tha Excel understands?
in other words how do I make Excel read what's in there now

I thought that's what I had posted, but in a forum the cell A1 is always the 'assumed' cell of reference


=A1<(NOW()-30)

To apply this to a range see my first post.

---

note, I would reccommend the formula that is easy to understand and Says whet you Mean

=NOW()-G20>30

may seem to be confusing to you later.

=G20<(NOW()-30)

is more easy to understand next year when you are trying to work out what you did.

Originally Posted by Carim

=AND(A1<(NOW()-30),A1<>"")

As Bryan pointed out ... A1 is the generic cell to be replaced by your cell

HTH
Carim

Another option if you are doing a range, is to exclude 'real' numbers and test for just dates by selecting the range in which your dates might occur, then (with A1 as the active cell):

=AND(A1>30000,A1<(NOW()-30))

which dates back to Feb '82 from 30 days ago, but ignores most numeric value.

(note: whilst we use the generic A1 you can select any range, but adjust the formula to suit, so if you select G2 to H19 with G2 as the active (odd-colour = highlighted) cell, use the formula =AND(G2>30000,G2<(NOW()-30))
---

Originally Posted by pelachrum

all right, this works great thank you guys, if I could bother you about one more thing...

i want the formating like described above but NOT if another cell (H) in the same row is filled with data.

ie.
if my range is in the G column and the data in G15 for instance is past 30 days (therefore it's formated) and I enter data in H15, the formating in G15 resets to default.

thank you

My preference would be to move your current condition (for G) to Condition 2, and re-set condition 1 with a H15 test with no format set, thus, only if the first condition fails does the current (new Second) condition come into force to colour the cell Green.

=H15<>""
no format

etc

(the other option is to include another 'And' on the current condition, but it could start to look messy very quickly if you later decide on options for column H entries)

hth
---