Formula for date field

[Sunayan Sanatani]

1.I have simple dates in one column (say column A) .
2.In the next column(Column B) I would like the date five months after
Column A to be displayed.Eg if Column A has an entry of 9th June
2007,Column B should display 8th November,2007.
3.A simple formula does not do the job as this does not take into
account the different number of days in different months!

regards

S.Sanatani

 

[JE McGimpsey]

Your post is a bit ambiguous since you don't really say how the
different number of days in months should be handled.

One way:

A1: <date>
B1: =DATE(YEAR(A1),MONTH(A1)+5,DAY(A1)-1)

 

[Guest]

The formula below is required Analysis TookPak add-in

=EDATE(A1,5)-1

 

[Stan Brown]

Sat, 09 Jun 2007 08:13:15 +0530 from Sunayan Sanatani

1.I have simple dates in one column (say column A) .
2.In the next column(Column B) I would like the date five months after
Column A to be displayed.Eg if Column A has an entry of 9th June
2007,Column B should display 8th November,2007.

Is that a typo? Five months after June 9 is November 9, not November
8.

3.A simple formula does not do the job as this does not take into
account the different number of days in different months!

When you say "five months", then, what do you actually mean? 150
days? 5/12 of 365 days? Other?

 

[Sunayan Sanatani]

Is that a typo? Five months after June 9 is November 9, not November
8.

That this is not a typo is the main problem!
The actual job undertaken by me is done this way-A certificate is issued
on completion of a survey and this is valid for a period of five months
and these five months are counted as above (eg issued on June 9th 2007
and valid upto November 8th 2007 , 17th January to 16th June 2007 etc.)I
want to enter only the issue date in A1 and want the excel sheet to
calculate the validity date of the certificate in A2 using a formula.
Let me try out some of the formulas that have been suggested in the
other replies.

regards

S.Sanatani

 

[JE McGimpsey]

Is that a typo? Five months after June 9 is November 9, not November
8.

Hmmm... then what's five months after January 31? or twelve months after
February 29th?

Months are, as you've noted, slippery concepts. It's often folly to make
definitive statements without explicit specifications...

 

[Stan Brown]

Sat, 09 Jun 2007 22:59:02 +0530 from Sunayan Sanatani

A certificate is issued
on completion of a survey and this is valid for a period of five months
and these five months are counted as above (eg issued on June 9th 2007
and valid upto November 8th 2007 , 17th January to 16th June 2007 etc.)I
want to enter only the issue date in A1 and want the excel sheet to
calculate the validity date of the certificate in A2 using a formula.

Okay, so you actually mean five months less a day.

Make sure you experiment with issue dates like September 30, 2004(*)
and May 31 of any year.

(*)September 30, 2007 won't reveal a problem.