Hi
have the following date format in A1 - 12/15/05.
In b1 I want the date calculated as 6 months later, entered the following in
b1-
=DATE(,,A1+180), but it returns #NUM. Tried it as +6 but still same result
What am I doing wrong

kit wrote:
Hi
have the following date format in A1 - 12/15/05.
In b1 I want the date calculated as 6 months later, entered the following in
b1-
=DATE(,,A1+180), but it returns #NUM. Tried it as +6 but still same result
What am I doing wrong

Hi kit,

Try =date(year(A1),month(A1)+6,day(A1))

Be careful concerning :
* 6 months < 180 days
* 31 aug + 6 months is not 31 feb :o)

FxM

Thank you, works fine now. Take your point on the returned date not being
exact, for now we will have to live with it.
thank you for your help
Kit

"FxM" wrote:

kit wrote:
Hi
have the following date format in A1 - 12/15/05.
In b1 I want the date calculated as 6 months later, entered the following in
b1-
=DATE(,,A1+180), but it returns #NUM. Tried it as +6 but still same result
What am I doing wrong

Hi kit,

Try =date(year(A1),month(A1)+6,day(A1))

Be careful concerning :
* 6 months < 180 days
* 31 aug + 6 months is not 31 feb :o)

FxM

On Sun, 23 Oct 2005 03:28:02 -0700, kit wrote:

Hi
have the following date format in A1 - 12/15/05.
In b1 I want the date calculated as 6 months later, entered the following in
b1-
=DATE(,,A1+180), but it returns #NUM. Tried it as +6 but still same result
What am I doing wrong

If you do not care about end of the month issues (e.g. 6 months after 31 Aug
2004 will compute to 3 Mar 2005) then:

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

---------------------
If you do care about those issues, and if you have the ATP (Analysis Tool Pak)
installed, you can use:

=EDATE(A1,6)

If this function is not available, and returns the #NAME? error, install and
load the Analysis ToolPak add-in.

How?

On the Tools menu, click Add-Ins.
In the Add-Ins available list, select the Analysis ToolPak box, and then click
OK.
If necessary, follow the instructions in the setup program.
----------------------

Or, without the ATP, you can use the formula:

=DATE(YEAR(A1),MONTH(A1)+6,MIN(DAY(A1),
DAY(DATE(YEAR(A1),MONTH(A1)+7,0))))


--ron

Hi,
Perhaps this approach (took from another from Tom Ogilvy) has in
consideration month February (29 or 28 days):
=DATE(YEAR(A1);MONTH(A1);DAY(A1))+180+CHOOSE(WEEKD AY(DATE(YEAR(A1);MONTH(A1);DAY(A1)+180));1;0;0;0;0 ;0;-1)

Regards.

--
JRod
Microsoft MVP - Excel
http://EXCELer.blogspot.com

"FxM" escreveu na mensagem
...
kit wrote:
Hi
have the following date format in A1 - 12/15/05.
In b1 I want the date calculated as 6 months later, entered the following
in b1-
=DATE(,,A1+180), but it returns #NUM. Tried it as +6 but still same
result
What am I doing wrong

Hi kit,

Try =date(year(A1),month(A1)+6,day(A1))

Be careful concerning :
* 6 months < 180 days
* 31 aug + 6 months is not 31 feb :o)

FxM

On Sun, 23 Oct 2005 15:56:21 +0100, "JRod" wrote:

Hi,
Perhaps this approach (took from another from Tom Ogilvy) has in
consideration month February (29 or 28 days):
=DATE(YEAR(A1);MONTH(A1);DAY(A1))+180+CHOOSE(WEEK DAY(DATE(YEAR(A1);MONTH(A1);DAY(A1)+180));1;0;0;0; 0;0;-1)

Regards.

Have you tried it with other than an end of the month date?

For example:

A1: 15 Aug 2004 -- 11 Feb 2005

I would expect that the OP would prefer the result to be 15 Feb 2005.


--ron

Hi, Ron,
ok, with this formula, gets 11 Feb 2005, but this is right 180 days after 15
Aug 2004, isn't it?
I'm sure that you are right with the preference, but as Kit wrote 6 months
or 180 days...

Regards.

--
JRod
Microsoft MVP - Excel
http://EXCELer.blogspot.com

"Ron Rosenfeld" escreveu na mensagem
...
On Sun, 23 Oct 2005 15:56:21 +0100, "JRod" wrote:

Hi,
Perhaps this approach (took from another from Tom Ogilvy) has in
consideration month February (29 or 28 days):
=DATE(YEAR(A1);MONTH(A1);DAY(A1))+180+CHOOSE(WEE KDAY(DATE(YEAR(A1);MONTH(A1);DAY(A1)+180));1;0;0;0 ;0;0;-1)

Regards.

Have you tried it with other than an end of the month date?

For example:

A1: 15 Aug 2004 -- 11 Feb 2005

I would expect that the OP would prefer the result to be 15 Feb 2005.


--ron

On Sun, 23 Oct 2005 18:25:12 +0100, "JRod" wrote:

Hi, Ron,
ok, with this formula, gets 11 Feb 2005, but this is right 180 days after 15
Aug 2004, isn't it?
I'm sure that you are right with the preference, but as Kit wrote 6 months
or 180 days...

Regards.

--
JRod
Microsoft MVP - Excel

Maybe it's the English language, but when I the OP wrote:

In b1 I want the date calculated as 6 months later

That's what I assumed. The 180 reference I saw in his post I interpreted as
being part of his initial attempt to achieve his requirement.


--ron