Conditional Formatting, Formula Help Needed

  • Thread starter Thread starter Kirstie Adam
  • Start date Start date
K

Kirstie Adam

Dear All,

I have a spreadsheet in which i want to use conditional formatting.

I have one cell which display's today's date, Now(), formatted dd/mm/yy.
I have a column of dates from which we rec'd a job request, cells B2:B22. I
want to say:

If today's date is equal to B2 plus 17 or 18 or 19, colour yellow, or else
leave blank.

Can anyone help??

TIA,

Kirstie
 
Kirstie

Select the range. Go to Format / Conditional Formatting and select 'Cell
value is between' and in the first box type =today()-17 and in the second
today()-19
You can then set the format as you wish.

Andy.
 
Andy,

Thanks, but i think i haven't said my problem clearly!
The cell colour formatting is in the cell NEXT to the cell with the info in
it:

eg Job Req Rec'd Actioned
(date format) (if today's date is between 17 and
19 days after rec'd job request,
turn yellow)

I think this alters whatg you suggested, as i can't use "Cell Value Is" as
it is based on the previous cell's value, if you see what i mean.

Apolgies for not being more clear, but i would appreciate as much help as
you can give me!

Thanks,

Kirstie
 
Kirstie

OK, you'll need to select Formula Is. I've used cell C21 and typed:

=AND(TODAY()-17>=$B$21, TODAY()-19<=$B$21)

This will test the cell B21 and colour C21.

Andy.
 
Andy,

Thanks so much, worked great!

Kirstie

Andy B said:
Kirstie

OK, you'll need to select Formula Is. I've used cell C21 and typed:

=AND(TODAY()-17>=$B$21, TODAY()-19<=$B$21)

This will test the cell B21 and colour C21.

Andy.
Click to expand...
 
Andy,

Eh, come across a small problem, that bit worked fine, but now my bosses
want to do more!
If you (or anyone) can help me on this, that would be great!

I want to say:


if today()-17>=B5 or today()-18>=B5 colour B6 yellow, but only if B6 is
Blank, if B6 is not blank, colour grey
OR
if (B5+20)<=today(), colour B6 red, but only if B6 is blank, if B6 has
anything in it, colour grey


This is a bit on the complex side for me, could manage it in access, but
excel is all new to me, any help greatly appreciated

Kirstie
 
Back
Top