Date+ returns #Num

[kit]

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

 

[FxM]

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 )

FxM

 

[kit]

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

 

[Ron Rosenfeld]

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

 

[JRod]

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(WEEKDAY(DATE(YEAR(A1);MONTH(A1);DAY(A1)+180));1;0;0;0;0;0;-1)

Regards.

 

[Ron Rosenfeld]

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(WEEKDAY(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

 

[JRod]

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.

 

[Ron Rosenfeld]

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.

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