Chris,

In AR1, enter 170.
In AS1, enter 140
In AT1, enter 110
In AU1, enter 80
In AV1, enter 50
In AW1, enter 20
In AX1, enter 0

In AR3, enter the formula

=IF($AQ3="","",IF(AND($AQ3<=TODAY()+AR$1,$AQ3TODA Y()+AS$1,$X3="VACANT"),TRUE,""))
Then copy AR3 to AT3:AW3, and then copy AR3:AW3 down for as many rows as you
need.

HTH,
Bernie
MS Excel MVP


"Chris" wrote in message
...
Hi Bernie,

AQ3 is 01 Dec 2009.

170 days before 01 Dec 2009 is 14 Jun 2009. let us say that X3 is
"VACANT".

If we are looking at the workbook on 13 Jun 2009 (171 days before 01 Dec
2009), then we would like to see in AR3 FALSE.

If we are looking at the workbook on 14 Jun 2009 (170 days before 01 Dec
2009), then we would like to see in AR3
TRUE.

If we are looking at the workbook on 15 Jun 2009 (169 days before 01 Dec
2009), then we would like to see in AR3
TRUE.

We would like to see TRUE in cell AR3 for all dates 141 to 170 days
prior to 01 Dec 2009 and where X3 still shows
"VACANT".

We undertake 5 separate checks on this workbook. For example;

Our first check is at approximatelty 170 days before 01 Dec 2009 to see
if there are any cells in column X that are populate with the text:
"VACANT".

So, if we were to check the workbook at let's say 167 days before 01 Dec
2009 and cell X3 is still populated with
the text: VACANT", then AR3 should say: TRUE.

If however, on 169 days before 01 Dec 2009, cell X3 is populated with
the text: "FILLED", then AR3 should say:
FALSE.

We try to check the workbook on time but due to public holidays and
weekends, it is not always practical to check it exactly 170 days
before.

The same goes for 140 days before 01 Dec 2009. We would like to see
TRUE in cell AR3 for all dates 111 to 140 days
prior to 01 Dec 2009 and where X3 still shows "VACANT".

I hope this helps - it's difficult to explain - please feel free to ask
more questions,

I really appreciate your kind help,

Cheers,

Chris.



*** Sent via Developersdex http://www.developersdex.com ***