Conditional formating based on multiple criteria.

[mark.studley]

Hi

I have two queries I am looking for help with please. Both are conditional formatting queries.

The first is that I want to higlight a cell based on the date being older than say 6 weeks from today. However I have say six columns where date could be entered depending on which stage the job has reached. I only want date older than 6 weeks to be flagged if the cells after it do not contain any data. Just to complicate it slightly I need this to carry on every week so next week date 6 weeks before will then get flagged. Is this possible? (i'm using excel 2007)

The second querry I have is can i format a cell so it is highlighted only if data has been entered this week?

Thanks for any advice.

[zbor]

Can you upload example workbook with several different examples (you can paint cells manually).

[mark.studley]

Here is example data. Site A was recieved within the last 6 weeks so is not flagged for that. The date is more than a week old so is also not flagged.

The red cells are more than 6 weeks ago and have no date in the column to their right. Yellow are dates this week.

Is it possible to do this automatically?

[zbor]

Try for last 10 days to see does WEEKNUM criteria works as you planned

[mark.studley]

Thanks for the reply, it seems to work other than cell C2, which shouldnt have any colour as it is less than 6 weeks old?

[mark.studley]

Ah i see i have had a look at the formulas. Its red if it is the final number in the line. If there was a third condition saying If date is more than toady minus 35 days for example, make the cell blank would that work?

[zbor]

try this Red criteria:

Please Login or Register  to view this content.

[mark.studley]

That works perfectly thanks so much for that, really appreciated!

[mark.studley]

Sorry I have another question on this. The guys i am working with noticed that in certain cirumstaces we will sometimes have instances where a stage is skipped so only dates for say stage 1 and then stage 6 are entered.. Can i edit the formula for the red so that if there is data in any of the cells and not just the next one to the right, the formula will highlight only the last one?

[zbor]

Here, try this (always one more column of table range and that one must not have number/date. In this case H column):

Please Login or Register  to view this content.

[mark.studley]

Perfect again. Thank you.

[mark.studley]

Hi,

I've managed to apply the rules to my sheet with real data. For some reason though the red formula is not picking up any dates before this year in mine, but is working for the dates this year, as is the other rule. Is this a setting on the sheet that needs changing?

The formula for mine I have is

=(WEEKNUM(TODAY(),2)-WEEKNUM(N6,2)>=6)*ISNUMBER(N6)*(COUNT(O6:$Y6)=0)

Thanks

[mark.studley]

Still a little stuck with this so if anyone has any ideas they would be much appreciated.

[zbor]

You'll need to go with real dates but you will loose for 'current week':

Change number of days (now is 40):

Please Login or Register  to view this content.

[mark.studley]

Brilliant that is working thank you again zbor.