Add 6 months

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I would like to increase a cell containing a date by 6 months, to be shown in the next column. As far as I can +6 would increase it by 6 days. Is there a formula to increase the months rather than the days?
 
Hi

One way:

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

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

Lawlera said:
Hi,

I would like to increase a cell containing a date by 6 months, to be shown
Click to expand...
in the next column. As far as I can +6 would increase it by 6 days. Is
there a formula to increase the months rather than the days?
 
Hi Lawlera!

With Analysis ToolPak you can use:

=EDATE(A1,6)

But without Analysis ToolPak, on most people's understanding of a date
6 months hence you need:

=DATE(YEAR(A1),MONTH(A1)+6,MIN(DAY($A$1),DAY(DATE(YEAR(A1),MONTH(A1)+7
,0))))
It assumes that if the DoM of base month does not exist in the month,
then the last day of that month should be returned.
If A1 is 31-Aug-2003 the formula will return 29-Feb-2004 (the same as
EDATE)

Watch out for:

=DATE(YEAR(A1),MONTH(A1)+6,DAY(A1))
It gives difficult to interpret dates if A1 is (e.g.) 31-Aug-2003 it
returns 2-Mar-2004

In the form given, the formula can be copied down or across to give a
series of dates 6 months apart.


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top