Adding 3 months to a date

A

AlanN

I have need of creating a formula that displays the "MMM-DD" of 3 months later than the adjacent cell.
Example:
If 02/15/2004 was in cell A1, what formula would I have to have in cell B1 to display May-15?

TIA, AlanN
 
A

Arvi Laanemets

Hi

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


--
Arvi Laanemets
(Don't use my reply address - it's spam-trap)


I have need of creating a formula that displays the "MMM-DD" of 3 months
later than the adjacent cell.
Example:
If 02/15/2004 was in cell A1, what formula would I have to have in cell B1
to display May-15?

TIA, AlanN
 
F

Frank Kabel

Hi Arvi
this could lead to problems if A1 contains a month end. e.g.
11-30-2003. Adding 3 months would lead to 02-30-2004 which would be
03-01-2004
 
A

AlanN

Hi Frank, how's the weather in Germany? It's wet and dreary and cold in
Toronto, Canada.

I am trying to ignore the year in my request. I am setting up a simple
review schedule for a long term care facility.
Health care compliance requires that:
- 6 weeks after an admission date, a family interview must take place
- quarterly reports are scheduled for the resident 3,6,9 and 12 months from
the admission date.
regardless of the year of the admission date, the quarterly reports will
(should) occur on the month and day based on the initial month and day of
the admission.

Sample (I left out any names to the left of Rm#):

Rm# AdmitDate InterviewDateDueON QTRLY1 QTRLY2 QTRLY3 Annual
717L November 20, 2003 = previous cell+42 days =November+3
mths & 20 =Nov+6 & 20 =Nov+9 & 20 =Nov+12 &
20
612 November 21, 1997 Jan 02/1998 Feb 21 May 21 Aug 21 Nov 21
304 November 27, 2003 etc.
305L November 29, 1999
515 November 30, 2002
505R December 7, 1993
608L December 11, 2003
616L December 12, 2003
516R December 13, 2003
501 December 14, 1999

The reason I cannot use the years is that the admission date can be any
year - some going back >15 years.
Every year the reviews have to happen on a quarterly basis based revolving
around the admission month and day.

Can you help?

TIA, AlanN
 
N

Norman Harker

Hi Arvi, Frank and Alan!

To avoid the problems where day number >=29 use:

=DATE(YEAR(A1),MONTH(A1)+3,MIN(DAY($A$1),DAY(DATE(YEAR(A1),MONTH(A1)+4,0))))

The general formula or one which allows you to vary the number of
months to be added is:

=DATE(YEAR(A1),MONTH(A1)+AddMons,MIN(DAY($A$1),DAY(DATE(YEAR(A1),MONTH(A1)+AddMons+1,0))))

And here’s an alternative that does the same by Peter Dorigo:

=MIN(DATE(YEAR(A1),MONTH(A1)+ AddMons +{1,0},DAY($A$1)*{0,1}))


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Top