Formula for date field

  • Thread starter Thread starter Sunayan Sanatani
  • Start date Start date
S

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

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

When you say "five months", then, what do you actually mean? 150
days? 5/12 of 365 days? Other?
 
Stan said:
Is that a typo? Five months after June 9 is November 9, not November
8.
Click to expand...

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
 
Stan Brown said:
Is that a typo? Five months after June 9 is November 9, not November
8.
Click to expand...

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

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