Change colour based on date

[grateful]

Hi,

I would like to make the colour of the font change in a cell based on the
date.

In workbook 1 I have a date in Cell A1

In workbook 2 that date appears in cell B1

If today is Monday,December 15th, 2008 and the date in B1 is between
Tuesday, December 9th, 2008 and December 15th, 2008 than I would like that
date to appear "green" in workbook 2 cell B1

But if the date in cell B1 is before Tuesday December 9th, 2008 I want it to
appear in white.

The thing is the formula can't say specific dates, like December 15th, 2008
- Because every week the dates will change.

It needs to be the present Monday back to the past Tuesday.

The reason being: Between Thursday evening and Monday evening I have time to
update reports. They do not have to be updated for anyone until Tuesday
morning.
But, if I do have them done early, such as on Sunday night, I would like it
to be apparent in "green" that the info has been updated; so they can use
those figures on Monday if they open the report and see it "green".

If it is Monday and someone opens the report and the date is still the
Monday previous, it should be white...and then they will know not to use the
figures, they haven't been updated.

Can anyone help me with this?

Thank you so much.

If the date in B1

If the

 

[Shane Devenshire]

Hi,

First, it is not clear how you are using cell A1, you mention it at the
beginning of the post but never again?

Suppose you are going to put the critical dates in cells F1:F2 and you want
to format B2. Select B2, choose Format, Conditional Formatting, leave
between selected and click in the 3rd box, click on cell F1, then click in
the 4th box and click on cell F2. Click the Format button and pick your
color.

Every week you can specify the F1 and F2 dates or you can design a formula
to figure them out for you. I'm not entirely clear on what your conditions
are so I leave that to you.

 

[grateful]

Hi,

Sorry...I don't think I was very clear on what I needed.

Workbook 1 is the workbook I work in.

Workbook 2 is the workbook others work in.

Between Thursday and Monday of the current week I will update my information
and I will put the date I have completed it, in cell A1 which is therefore
going to appear in cell B2 of workbook 2.

If I am another person and I open workbook 2 on Monday the 15th aka "Today",
but Grateful hasn't updated the report yet, and the date still says Monday
the 8th, I need the font in B1 to be white.

If it has been updated, when workbook 2 is opened on Monday the 15th, I
would like the font to automatically turn to Green.

Basically:
I am looking for something in workbook 2, cell B1 to look and analyze
automatically if the date in B1 is between Today() and 6 days previous; if it
is make the font "Green", if it isn't make it "White"

P.s. Even though your suggestion wasn't exactly what I need...I still
couldn't get it to work....I was curious...because maybe it would help with a
plan B.

Any help would be great.

Thank you so much.

 

[xlm]

Select cell B1 in workbook 2, then go the Format
select Conditional Format
select Formula Is and place tyour formula in it
in Format, choose the color you want.
then click Add and place your other condition
OK out

HTH

--

Thank You

cheers,

========================
please click the Yes button if this help

 

[grateful]

Hi, Thank you for your help.

Can you suggest a formula that might work?

 

[grateful]

Hi,

I was able to get something working thanks to your help and other online
discussions.

Incase it's helpful to anyone else, this is what I did:

I made a date in cell C1 like this: =Today()
I made formatted cells B1 and C1 as Date
I made a formula in cell D1 like this: =C1-B1
and I formatted cell D1 as General

I than clicked on cell A2 and unfer Formatting I selected Conditional
Formatting

I selected Formula is
and typed: =AND(D1>.01,D1<7)
clicked on Format, then Pattern, then selected the colour Green, and OK

I then pressed Add to add another condition

I selected Formula is
and typed: =OR(D1=7,D1>7)
clicked on Format, then Pattern, then selected the colour Red, and Ok

Then I saved it...and it worked.

I chose to use 7, because for me if the answer was between 1 and 6 days in
cell D1, I knew that the report had been updated that week.

Thank you for all your help.

 

[xlm]

Thank you for your feedback, I am glad to be of help.


--


cheers,

========================
please click the Yes button if this help