5 months prior to date macro help please

[Chris]

Hello, could someone please help me with the following? I need a macro
that looks up column AQ (a date column).

I then need the macro to calculate the date five-months prior to the
dates in range AQ3 : AQ65000.

With the new calculated date (five-months prior to date) - I need to
determine if the associated values in column X contain the text
"VACANT".

So I need the macro to lookup column X (a text column) and if any of the
cells in range X3 to X65000 contain the text "VACANT" then that record
needs to be copied to another open workbook named 5 Months Out.xls, and
onto worksheet named: 5 Months Out. The copied rows need to be pasted
onto the new worksheet starting at cell: A3.

For example: cell AQ5 contains the date: 01-Dec-09. The date
five-months prior to this would be: 01-Nov-09. In Column X there are
cells in range X3:X65000 that contain the text "VACANT". These are
located in cells: X5 and X24. Thus, the entire rows (row 5 and row 24)
need to be copied to the open workbook named 5 Months out.xls onto
worksheet named: 5 Months Out into cell A3 (all of row 3).

I am using Excel 2003 with Win XP.

Any help in this macro would be greatly appreciated.

Kind regards,

Chris.

 

[Bernie Deitrick]

Chris,

Just a clarification:

For example: cell AQ5 contains the date: 01-Dec-09. The date
five-months prior to this would be: 01-Nov-09.

Five months prior to 01 Dec 09 is 01 July 09. But 5 months from today is 28 Oct 09 - which date do
you want to use as the basis of your comparison?

Anyway, once you decide, the way to approach this problem is not to use a macro but to have a column
of formulas:

=AND(AQ3 >= DATE(YEAR(TODAY()),MONTH(TODAY())+5,DAY(TODAY())),X3="VACANT")

Copied down to match your data set, then apply a data filter on that column, choose TRUE and then
copy only the rows that are visible after the filtering.

HTH,
Bernie
MS Excel MVP

 

[Chris]

Hi Bernie, thanks for you help - very much appreciated. I sought
clarification from my work supervisor as follows:

Column AQ is headed: "Planned date out of country" (cell: AQ2). Column
X is headed: "Position" (cell X2). Not all

the cells in column AQ are populated with a date.

We need know the following please:

170 days out from the dates showing in column AQ all those records that
are populated with the text "VACANT" in column X. If this condition is
true, then the corresponding cell in column AR needs to be populated
with the result of true. Column AR is headed: "170 Days Out".

140 days out from the dates showing in column AQ all those records that
are populated with the text "VACANT" in
column X. If this condition is true, then the corresponding cell in
column AS needs to be populated with the result of true. Column AR is
headed: "140 Days Out".

110 days out from the dates showing in column AQ all those records that
are populated with the text "VACANT" in
column X. If this condition is true, then the corresponding cell in
column AT needs to be populated with the result of true. Column AR is
headed: "110 Days Out".

80 days out from the dates showing in column AQ all those records that
are populated with the text "VACANT" in
column X. If this condition is true, then the corresponding cell in
column AU needs to be populated with the result of true. Column AR is
headed: "80 Days Out".

50 days out from the dates showing in column AQ all those records that
are populated with the text "VACANT" in
column X. If this condition is true, then the corresponding cell in
column AV needs to be populated with the result of true. Column AR is
headed: "50 Days Out".

I hope this clarifies what we need. If not, please do not hesitate to
contact me.

Many thanks,

Chris.

 

[Bernie Deitrick]

Chris,

I would use something like this in AR3:

=IF(AQ3="","",IF(AND(AQ3<=TODAY()+170,X3="VACANT"),TRUE,""))

Change the 170 to 140 for use in AS3, and 110 for using in AT3.... etc.
Then copy those formulas down the column to match your data set.

And by 'days out' I hope you mean 170 days from today, etc.

HTH,
Bernie
MS Excel MVP

 

[Chris]

Hi again Bernie, thanks so much for you excellent assistance - greatly
appreciated. It's probably my fault, but it is 170 days from the date
showing in column AQ. Would you be able to please tweak your formula
accordingly?

Many Thanks,

Chris.

 

[Bernie Deitrick]

Chris,

You wrote

"it is 170 days from the date showing in column AQ."

I am assuming "it" means _today_. Could it be a date in another cell?

If "from the date" means "if it (today) is 170 or fewer days _before_ the
date in AQ3"

=IF(AQ3="","",IF(AND(AQ3<=TODAY()+170,X3="VACANT"),TRUE,""))


If "from the date" means "if it (today) is 170 or more days _after_ the
date in AQ3"

=IF(AQ3="","",IF(AND(AQ3+170 <=TODAY(),X3="VACANT"),TRUE,""))

If you only want 'it' to be exactly 170 days before or after, remove the <
part of the comparison <=

If neither of those is what you actually want, give me some example dates
and when you want TRUE to show up....

Bernie
MS Excel MVP

 

[Chris]

Hi Bernie, Oops, I am sorry I have not made myself very clear -
sometimes it is dificult to explain what you want

when a picture speaks a thousand words. Here is an example: (please
note that today's date is not used in the

equation). We need the formula to be in column AR for the following
example:

In column X at cell X3 is the text: "VACANT". In column AQ at cell AQ3
is the date: 01 Dec 09. We need to know 170

days out from the date showing in column AQ (which in this example is:
01 Dec 09), that the corresponding value in

cell X3 contains the text: "VACANT" (which for this example it does).
Thus, the corresponding cell at AR3 (using

your formula) would be popluated with TRUE.

In column X at cell X4 is the text: "FILLED". In column AQ at cell AQ4
is the date: 05 Dec 09. We need to know 170

days out from the date showing in column AQ (which in this example is:
05 Dec 09), that the corresponding value in

cell X4 contains the text: "FILLED" (which for this example it does).
Thus, the corresponding cell at AR4 (using

your formula) would be popluated with FALSE.

In column X at cell X5 is the text: "VACANT". In column AQ at cell AQ5
is the date: 12 Dec 09. We need to know 170

days out from the date showing in column AQ (which in this example is:
12 Dec 09), that the corresponding value in

cell X5 contains the text: "VACANT" (which for this example it does).
Thus, the corresponding cell at AR5 (using

your formula) would be popluated with TRUE.

Does this example help clear things up?

Kind regards,

Chris.

 

[Bernie Deitrick]

Chris,

You say that today's date is not relevant.... what date is relevant?

AQ3 is 01 Dec 2009.

170 days before 01 Dec 2009 is 14 Jun 2009. Let's say that X3 is "VACANT"

If you are looking at the workbook on 13 Jun 2009 (171 days before 01 Dec
2009), what do you want to see in AR3? TRUE - or - FALSE?

If you are looking at the workbook on 14 Jun 2009 (Exactly 170 days before
01 Dec 2009), what do you want to see in AR3? TRUE - or - FALSE?

If you are looking at the workbook on 15 Jun 2009 (169 days before 01 Dec
2009), what do you want to see in AR3? TRUE - or - FALSE?

WHEN do you actually want to see TRUE in cell AR3?

Bernie

 

[Chris]

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.

 

[Bernie Deitrick]

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,$AQ3>TODAY()+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]

Hi Bernie, thanks so much for your excellent assistance. I am having a
minor trouble getting your formula to work. It may be the way I copied
it into the workbook. In cell AR3 where I first copies your formula,
the results of either True or False do not appear in that cell (the cell
is blank). Cell X3 is showing text: "VACANT" and cell AQ3 has the date
01 Dec 09 in it. I do not understand why cell AR3 is blank?

Would it be possible for you to please explain in general terms (I am a
newbie) why column AS is not populated with your formula? Also, do we
need cell AX1 to be populated by the number zero?

I am trying to understand as best I can your formula so that I may learn
more about Excel formulas.

Again, I thank you. It is sometimes not easy to understand what people
need when replying back to their Excel problems. Thanks for your
understanding and patience.

Kind regards,

Chris.

 

[Chris]

Hi Bernie, I did some extensive testing of your formula to determine
exactly where it is showing me blank cells. I changed the actual date
on my PC to simulate the dates 170, 110, 80, 50, 20 days before 01 Dec
09. I then pressed F9 to refresh the worksheet. All of the dates
worked well. Sorry about all that. Thanks again for all your help and
assistance - you did a really terrific job and it is greatly
appreciated.

Many thanks,

Chris.

 

[Bernie Deitrick]

Chris,

I'm glad to hear that you got it to work... Congratulations!

A slightly easier test of your formulas would be to change the date in cell
AR3. Another test would be to copy the formulas down for 184 rows, then
enter 30-Nov-09 in cell AR4, then select AR3:AR4, grab the fill handle, and
pull down for 183 rows. That would give you the entire spectrum of date
values between now and 01 Dec 09, and the TRUE pattern in your formulas
would be easy to decipher.

Bernie

 

[Patrick Molloy]

there is a DATEDIFF function that you can use in VBA