Calculation of future dates


M

Michael

Hello,

I have a list of clients with dates of our first contact, e.g.
J. Smith 2008 Apr 12
K. Brown 2008 June 14
A. Black 2008 July 03
S. Long 2008 July 24
J. Doe 2008 Aug 01
A. Miller 2008 Sept 06

Then I need to contact them every 3 months for 2 years (e.g. both A. Black
and S. Long should be contacted in Oct 2008, Jan, Apr, Jul and Oct 2009, Jan,
Apr and finally July 2010).
How can I make Excel to put dates of prospective contacts - I need the month
and year only - automatically as soon as the first contact date is entered
for each of them?
I would like to enter the name in column A, first contact date in column B,
and have prospective dates appear automatically in columns C-J.

Then, I would like to know the number and kind of each contact for a given
month.
I imagine a worksheet with contact names in rows (3mo, 6mo, 9mo, 1yr etc.)
and months in columns (Oct 08, Nov 08, Dec 08, Jan 09 and so on).
For example the column "Jan 09" would have a "1" in 9mo contact cell, a "2"
in the 6mo contact cell and a blank 1yr contact cell. How to do it?

I am sorry if my 2 questions are too long, I tried to explain as clear as
possible.
 
Ad

Advertisements

B

Bernie Deitrick

Michael,

With the date in cell B2, enter this into C2, and format for month/year from the date format
options. Then copy to the right for 8 columns.

=DATE(YEAR(B2),MONTH(B2)+3,1)

HTH,
Bernie
MS Excel MVP
 
Ad

Advertisements

M

Michael

Dear Bernie,

Thank you, the formula answers my needs perfectly. Could you please help me
also with the second part of my question - the calculation of contacts per
month? I would like to have something like this:

Sep08 Oct08 Nov08 Dec08 etc.
3mo 2 0 0 0
6mo 1 1 4 2
9mo 0 3 1 0
etc.

The above example would show a part of table for clients first contacted
between January and June 2008 with 3 clients in Jan08, 4 clients in May08, 2
in Jun08 and one in each of the remaining months. Please note that e.g. what
is 3mo in Sep08 becomes 6mo in Dec08.
Is it possible to draw such a table from the list of first contact dates?

I work with version 2003 - I forgot to mention it before.

Thank you,
M.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top