Conditional formatting

Resolved · High Priority · Version 2010

Thu 31 Jul 2014, 15:13 Delegate Jay said...

Jay has attended:
Excel Intermediate course
Excel Advanced course

Conditional formatting

Hi,

I think i spoke to doug before about this matter but i need a step further. i have the code, which is: =AND($I2>0,$I2<=TODAY()+7)

for the selected area, this would highlight the row if the date from Today was 7 days away (1 week).

I now need to include if its 1 month away (30 days) so if it was a week it would go one color but if its 1 month away it would go another?

I think i need to add to this code with an IF function but not sure how to go about it?

regards

Jay

Fri 1 Aug 2014, 10:53 Trainer Doug said...

RE: Conditional formatting

Hi Jay

To have Conditional Formatting display one colour for a month away and another colour for a week away I think you would need to create 2 separate rules.

Rule 1
=AND($I2>0,$I2<=TODAY()+7)

Rule 2
=AND($I2>0,$I2<=TODAY()+30)

Note that the rule for a week away must be the first condition. To check or change the order use Manage Rules and the arrow button to move up or down a condition.

Thanks for the question

Doug
Best STL

Fri 1 Aug 2014, 12:56 Delegate Jay said...

RE: Conditional formatting

Thanks i did do this, just didn't know that one had to be first.

thanks

Fri 1 Aug 2014, 12:56 Delegate Jay said...

RE: Conditional formatting

Thanks i did do this, just didn't know that one had to be first.

thanks

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Autonumber in Excel

To create an autonumber field, can use the Offset() function.

In cell A1, enter the number 1.
Then in cell A2, enter this formula:

=OFFSET(A2,-1,0)+1

Then copy the formula from cell A2, down as far as you need.

See also: Autonumber in Excel forum post.

View all Excel hints and tips