Formula for date field

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sunayan Sanatani - 09 Jun 2007 03:43 GMT

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

Reply to this Message

JE McGimpsey - 09 Jun 2007 03:47 GMT

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)

> 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
[quoted text clipped - 6 lines]
>
> S.Sanatani

Reply to this Message

Teethless mama - 09 Jun 2007 05:09 GMT

The formula below is required Analysis TookPak add-in

=EDATE(A1,5)-1

> 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
[quoted text clipped - 6 lines]
>
> S.Sanatani

Reply to this Message

Stan Brown - 09 Jun 2007 18:01 GMT

Sat, 09 Jun 2007 08:13:15 +0530 from Sunayan Sanatani
<sanatani@mtnl.net.in>:

> 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?

Signature

Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/

Reply to this Message

Sunayan Sanatani - 09 Jun 2007 18:29 GMT

> 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

Reply to this Message

Stan Brown - 10 Jun 2007 00:49 GMT

Sat, 09 Jun 2007 22:59:02 +0530 from Sunayan Sanatani
<sanatani@mtnl.net.in>:

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

Signature

Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/

Reply to this Message

JE McGimpsey - 09 Jun 2007 18:42 GMT

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

Reply to this Message

Rate this thread: