Conditional Formatting betwen two dates 2

[Bill4tektips]

I know this is an easy one but I am having a "Senior Moment". I need to set some conditional formatting to get the font to turn a different colour when the time between two dates is grater than a set amount of days. What I have tried so far is "=(TODAY())-(A1)=2" where A1 is the date originated and 2 is the time period. So if the entry has been open for 2 days I would like the font to change to a different colour.

 

[xlbo]

=Today()-A1>=2

should work fine - if you are applying this to a number of cells you will probably need to "absolute" the column ref so:

=Today()-$A1>=2

and of course, this can only be set within the "formula Is" element of CF rather than "Value Is"

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Bill4tektips]

Thanks xlbo but my problem is that I need to colour different cells if it is 3 days over or 5 days over so I can't have the >= sign it has to work on definite numbers.

 

[xlbo]

sorry - just thought the issue was that it may be more than 2 days and therefore getting missed. In which case there should be no problem with:

=Today()-$A1=2

Kinda depends if you have a date only in the field or whether it is a date/time field. If it is date/time then the number will never be exactly 2 so you may have to use:

=and(Today()-$A1>2,Today()-$A1<3)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Bill4tektips]

xlbo, The only way this works is to put the > sign in =Today()-$A1>=2. Ant other ideas how I might get around it? The cells are in Date format only.

 

[Bill4tektips]

xlbo, I opened up a another new spreadsheet and tried it in their and everything works fine. Thanks