EXcel Formula

[martins]

hi

can anyone help; I have a spreadsheet which lists the clients within
our business and the month that they are due to be visited - The client
name is in column a1 followed by 12 columns representing the months of
the year b1-m1: at present, to record that a visit is due, the month is
manually entered in the approriate columns b1-m1 for example as "jan06"
in column b1; it is usually the case that a further visit will be due
in 3 months therefore, my question is this: I would like a formula that
will autumatically return "apr06" in cell e1 if "jan06" is entered in
cell b1 - i would like to show this in the format described and not as
a number -

Sorry if it sounds confusing - but can anyone help?

 

[Bob Phillips]

If you put a real date in B2 (you can format it as mmmyy), then you can use

=IF(MONTH(B2)+3=COLUMN()-1,TEXT(DATE(YEAR(B2),MONTH(B2)+3,1),"mmmyy"),"")

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Roger Govier]

Hi

I think Bob meant to put a $ sign in front of the column reference
before you enter the formula in C2 and copy across through D2:M2
=IF(MONTH($B2)+3=COLUMN()-1,TEXT(DATE(YEAR($B2),MONTH($B2)+3,1),"mmmyy"),"")

If you perhaps wanted to make it a little more flexible,and allow for
appointments which are any number of months ahead, then if you used
column C to record the number of months, 3, 6 or whatever then a
modification to Bob's formula
=IF(MONTH($B2)+$C2=COLUMN()-2,TEXT(DATE(YEAR($B2),MONTH($B2)+$C2,1),"mmmyy"),"")
pasted into D2 and copied across through E2:N2 would permit this.

 

[martins]

thanks for your help

do i literally need to enter the formula as is, or do I need to add
information to complete the equasion?

 

[Bob Phillips]

With Roger's mod for column,. enter as is.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)