Date+ returns #Num

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

Guest

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 said:
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
Click to expand...

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
 
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
Click to expand...

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

Regards.
 
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.
Click to expand...

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.
 
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.
Click to expand...
In b1 I want the date calculated as 6 months later
Click to expand...

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
 
Back
Top