How to select info based on date

[caseysmydog]

You helped us get this far...
Now, here's the story:
Our Excel list is about 125 people long. Each row has the person's last
name, first and date joined, xx/xx/xx. The program then calculates dates for
ea person as follows: 1 moth (from date joined), 3 months, 6 months, 9
months, 12 months and puts that in each cell across the row.

Name Date joined 1month 3 months 6months 9months
12months
Smith, Sam 10/12/08 11/12/08 1/12/09 etc etc
etc
Smith, Jane 09/14/08 10/14/08 12/14/08 etc etc
etc
-------------------------------------------------------------------------------
Smith, Sam 10/12/08 11/12/08
Here's what we hope we can do: Process the same data and have it list for ea
column person's who meet that column's criteria, 1e, is it one month from
date joined, then list name and joined date. Is it three months since date
joined, then list name date and joined date...and so on looking through
joined date till each person is scanned and those who meet formula are
listed.
Is it possible to do this?
Thanks very much...would be glad to clarify if we were not clear or would
love to hear if you have a better way.
David

 

[Peter T]

If I follow correctly -

=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))

Change A1 and 1 to suit

Regards,
Peter T

 

[caseysmydog]

Since each cell has the formula already in the cell, where would we put this
formula? We don't understand how you address a program that seems static with
it's instructions.
How do you cause the same program to do this suggested formula?
Thanks...be specific as possible

 

[Peter T]

=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))

Typically when formula solutions are suggested, such as the above, the data
cell to be processed goes in cell A1 and the formula can go anywhere, just
for testing. Later of course the reference(s) and formula location is
adjusted to needs.

So, try entering any date in cell A1. Paste the formula into another cell,
say B1. The date returned by the formula should show exactly one month after
the date in cell A1.

Say your first date cell is in C2. Enter the above formula in D2 but change
each instance of A1 in the formula to C1

Put a similar formula in E2, again with all references pointing to C2, and
change the +1 to +3 (ie to return a date 3 months after that in cell C2)

Repeat for other cells with +6, +9 etc

Select the formula cells and drag down.

People tend to prefer formula solutions where viable, but if you prefer a
programmatic solution you will need to give more details about locations of
your data.

Regards,
Peter T

 

[caseysmydog]

OK, the program we currently have:
Formula, =IF(ISBLANK(B2),"",DATE(YEAR(B2),MONTH(B2)+1, DAY(B2)))
A B C D E
F G
1 Name Joined 1 mth 3mth 6mth 9mth
12mth
2Ankers 2/11/08 3/11/08 5/11/08 8/11/08 11/11/08 2/11/09
3
etc.

We enter the name and date joined and months 1 through 12 are calculated.
There are about 125 names representing 125 rows and this makes up our master
list.
We send a letter after they've been a member 1 month, 3 months and so on.

Rather than go manually through the names and months, we would like to
process the file so that at the bottom of column C the names one month from
the joined date (column B) would print and the names 3 months from the joined
date (column B) would print at the bottom oc column D and so on.
We would process the data this way once a month.

Problem for us: We don't know how to address data that's already being
addressed by another formula, see top.
i.e. we have the above data and the formula works each time we enter a name
and date joined.
We sitting here looking at this file on the screen but don't have a clue as
to how we proceed to select the names as described above.

Thanks again....--
David

 

[Peter T]

Think I now have a better understanding of the objective. I'd approach it
like this -

Forget about date columns for 1, 3, 6 mths etc

Process-1
In (say) col D calc the next due date for a letter to be dispatched
In (say) col C calc the relevant number of mths, eg 1,3,6,9 etc
(could be formulas but I'd do this programmatically)

Process-2
Compare all dates in col-D with "today", if "due" extract the name and
values in col C & D and copy this temporary data to a new range (probably
the entire row relating to the name)
Run process-1 to update the "next" due date.

Process-3
Working with the temporary data, collate other required data, eg address
lines using look up tables

Process-4
Whatever is required to generate the letters. Maybe a letter template exists
in Excel and "batch process" or mail merge perhaps

Process-5 (optional)
record letter template name and date sent against each name.

Probably a good idea to give each name a unique ID. Maintain multiple data
tables for different types of data, all linked with the common ID.


There are many ways to approach this, the above is just for ideas.

Regards,
Peter T

 

[caseysmydog]

We're in over our heads...this looks like a better idea but we don't have a
clue how to go about doing this in Excel. (If we could just extract the name,
month ,1-3-6-9-12, we would be happy. Of course, what you suggest would be
wonderful.
Thanks

 

[Peter T]

To put everything together that I suggested is a complete project, somewhat
beyond the scope of a single ng question. If you have no or minimal
knowledge of VBA have you considered engaging a consultant. Something fairly
basic shouldn't take more than a few hours.

Otherwise have a look more closely at each of the steps of the approach.
Also consider how often you are likely to want to process, every day,
weekly, monthly etc. What type of interface etc, break down into component
parts. Should be doable !

Regards,
Peter T