Adding Dates

[gymwrecker]

I'm working on an excel spreadsheet that contains dates. If I have a cell with a specific date, how could, by adding 6 months, have the next cell display the new date? What formula, procedure should I use to add dates?, in this case, add six months to the date on one cell?

 

[Sam W]

=EDATE((A1),6) Or Just add the daye like in your formula in your last post.Good Luck

 

[Sam W]

=EDATE((A1),6)
If Cell A1 contains your orginal date, paste this formula in the cell you want the new date, Or Just add the days like in your formula in your last post.Good Luck, Sorry about the double post.
This message was edited by Sam W on 2002-04-28 19:27

 

[Yogi Anand]

On 2002-04-28 15:33, gymwrecker wrote:
I have cell J2 with 09 Nov 01, how could I make cell J3 read 09 May 02, J2 + 6 months?
I have tried all your given formulas, but none have worked so far! Thanks for your replies.

Hi gymwrecker:
I gave two formulas before that you can use.
If cell J2 is formatted as DATE, then you can use one of the following two formula to add six months to the date in cell J2. So in cell J3, put one of the following formulas:

=EDATE(J2,6) ... this will give you 09-May-02

=date(year(J2),month(J2)+6,day(J2)) ... this will also give you 09-May-02
You may have to format the cell J3 to show date in the format to your liking.

Hope This Helps!

 

[Yogi Anand]

Hi Sam W:
Sorry, I did not see your post till after I posted mine.

Regards!

Yogi Anand

 

[sreed]

On 2002-04-28 15:33, gymwrecker wrote:
I have cell J2 with 09 Nov 01, how could I make cell J3 read 09 May 02, J2 + 6 months?
I have tried all your given formulas, but none have worked so far! Thanks for your replies.

gym,

Yogi's suggestion is the most strightforward in my mind, i.e. use the following formula in J3:

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

This avoids assumptions about the lengths of months which other approaches seem to involve.

 

[gymwrecker]

Thanks a lot Yogi!!! Your formula worked instantly. It saved me a lot of work.
Now, is there a way to use multiple formulas within the same cell?

 

[rmtaylor]

Type this Formula in J3 to return the ans you requested

=DATE(YEAR(J2)+0,MONTH(J2)+6,DAY(J2)+0)

 

[Legacy 35]

On 2002-04-28 20:01, sreed wrote:

On 2002-04-28 15:33, gymwrecker wrote:
I have cell J2 with 09 Nov 01, how could I make cell J3 read 09 May 02, J2 + 6 months?
I have tried all your given formulas, but none have worked so far! Thanks for your replies.

gym,

Yogi's suggestion is the most strightforward in my mind, i.e. use the following formula in J3:

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

This avoids assumptions about the lengths of months which other approaches seem to involve.

Sorry, but I don't think your claim "This avoids assumptions about the lengths of months which other approaches seem to involve" is correct.

If the start date is, for example, 30 Aug 2002, the formula you propose will produce 2 Mar 2003.

Also, problems can arise with EDATE when the start date is the last day of a month.
With kudos to C. O. Jones, who has made many postings to this board about this problem, if the start date happens to be the last day of a month :

- if a result is expected which is also the last day of a month (e.g. 28 Feb 2002 + 6 months = 31 Aug 2002), then :
=IF(EOMONTH(A1,0)=A1,EDATE(A1+1,6)-1,EDATE(A1,6))

- if a result is expected which is not necessarily the last day of a month (e.g. 28 Feb 2002 + 6 months = 28 Aug 2002), then :
=EDATE(A1,6)

 

[sen_edp]

if you have ge starting date in A1

in a2 put this formula

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

it works

 

[Legacy 35]

On 2002-05-04 05:34, sen_edp wrote:
if you have ge starting date in A1

in a2 put this formula

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

it works

You say it works, but that depends upon what result the user expects.
Your formula does exactly the same (I think) as =EDATE(A1,6).
But see my previous posting - some users may expect results produced by =IF(EOMONTH(A1,0)=A1,EDATE(A1+1,6)-1,EDATE(A1,6))