Contract Expires 1st of 6th Month after Date entered!

I have a present date that when entered should bring up
an expiration date which is the first (1st) day of the
sixth (6th) month "after the end" of the month that the
contract originated in. Any thoughts!?

How about:

=DATE(YEAR(A1),MONTH(A1)+7,1)

Where A1 contained the original date.

Wayne wrote:

I have a present date that when entered should bring up
an expiration date which is the first (1st) day of the
sixth (6th) month "after the end" of the month that the
contract originated in. Any thoughts!?

--

Dave Peterson

Does the following do it for you - Not sure from your text whether I've gone a
month too far - Change the 7 to 6 if I have?

With start date in A1

=DATE(YEAR(A1),MONTH(A1)+7,1)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
Seasons Greetings and Very Best wishes to all :-)
----------------------------------------------------------------------------



"Wayne" wrote in message
...
I have a present date that when entered should bring up
an expiration date which is the first (1st) day of the
sixth (6th) month "after the end" of the month that the
contract originated in. Any thoughts!?


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.555 / Virus Database: 347 - Release Date: 23/12/2003

Thanks! That part works great. I have another
question...I want to expand upon the original !

If the contract is re-newed within 60 days of the expire
date, the six month term is valid from for the time
period as below.
A3_jAN 16 00 Actual Renew date
B3_MAR 01 00 Old Expire Date
C3_OCT 01 00 New Valid to Date

-----Original Message-----
How about:

=DATE(YEAR(A1),MONTH(A1)+7,1)

Where A1 contained the original date.

Wayne wrote:

I have a present date that when entered should bring up
an expiration date which is the first (1st) day of the
sixth (6th) month "after the end" of the month that
the
contract originated in. Any thoughts!?

--

Dave Peterson

.

Hi Wayne!

How about something like:

=IF(B1="Y",DATE(YEAR(A1),MONTH(A1)+13,1),DATE(YEAR (A1),MONTH(A1)+7,1))

But this requires a manual insert of Y in a renewal cell and has no
regard to renewal being within the time period.

There are a number of ways that could be addressed but it may be best
to decide "by human" whether a late renewal will be allowed.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"Wayne" wrote in message
...
Thanks! That part works great. I have another
question...I want to expand upon the original !

If the contract is re-newed within 60 days of the expire
date, the six month term is valid from for the time
period as below.
A3_jAN 16 00 Actual Renew date
B3_MAR 01 00 Old Expire Date
C3_OCT 01 00 New Valid to Date

-----Original Message-----
How about:

=DATE(YEAR(A1),MONTH(A1)+7,1)

Where A1 contained the original date.

Wayne wrote:

I have a present date that when entered should bring up
an expiration date which is the first (1st) day of the
sixth (6th) month "after the end" of the month that
the
contract originated in. Any thoughts!?

--

Dave Peterson

.

Thankyou again... I guess there are only two ways of
renewing. Early within 60 days; or on time or late.
What I am after is an un-arguable "valid to date",
arrived at from input of "actual" and "old expire" dates.
If early or "ON" time, then from the 1st of the following
month to the 1st of the seventh following month ( 6
months );or "ON" time and late, from the 1st of the
following month to the 1st of the seventh following month
( 6 months ). Now I'm getting confused!

-----Original Message-----
Hi Wayne!

How about something like:

=IF(B1="Y",DATE(YEAR(A1),MONTH(A1)+13,1),DATE(YEA R
(A1),MONTH(A1)+7,1))

But this requires a manual insert of Y in a renewal cell
and has no
regard to renewal being within the time period.

There are a number of ways that could be addressed but
it may be best
to decide "by human" whether a late renewal will be
allowed.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax
and Arguments)
available free to good homes.
"Wayne" wrote in message
...
Thanks! That part works great. I have another
question...I want to expand upon the original !

If the contract is re-newed within 60 days of the
expire
date, the six month term is valid from for the time
period as below.
A3_jAN 16 00 Actual Renew date
B3_MAR 01 00 Old Expire Date
C3_OCT 01 00 New Valid to Date

-----Original Message-----
How about:

=DATE(YEAR(A1),MONTH(A1)+7,1)

Where A1 contained the original date.

Wayne wrote:

I have a present date that when entered should
bring up
an expiration date which is the first (1st) day of
the
sixth (6th) month "after the end" of the month that
the
contract originated in. Any thoughts!?

--

Dave Peterson

.



.

Hi Wayne!

Maybe the best approach is to have a date for extension notice to be
given in A2 and then to may Y calculable:

A1:
Starting date
A2:
Renewal notice
B1:
=IF(A2="","",IF(A2DATE(YEAR(A1),MONTH(A1)+7,1)-60,"Y",""))
C1:
=IF(B1="Y",DATE(YEAR(A1),MONTH(A1)+13,1),DATE(YEAR (A1),MONTH(A1)+7,1))

If no notice is given, then A2 is empty and B1 will return "" which
gives "normal" expiry from C1
If notice is given, then if it's not less than 60 days from expiry, B1
will return Y and C1 will return "extended" expiry
If notice is given, then if it is less than 60 days from expiry, B1
will return "" and C1 will return "normal" expiry

But I can do away with the B1 formula using:

C1:
=IF(A2="",DATE(YEAR(A1),MONTH(A1)+7,1),IF(A2DATE( YEAR(A1),MONTH(A1)+7
,1)-60,DATE(YEAR(A1),MONTH(A1)+13,1),DATE(YEAR(A1),MON TH(A1)+7,1)))

I can't say I like that approach.

It's not been thoroughly tested so give it a good trial.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"Wayne" wrote in message
...
Thankyou again... I guess there are only two ways of
renewing. Early within 60 days; or on time or late.
What I am after is an un-arguable "valid to date",
arrived at from input of "actual" and "old expire" dates.
If early or "ON" time, then from the 1st of the following
month to the 1st of the seventh following month ( 6
months );or "ON" time and late, from the 1st of the
following month to the 1st of the seventh following month
( 6 months ). Now I'm getting confused!

-----Original Message-----
Hi Wayne!

How about something like:

=IF(B1="Y",DATE(YEAR(A1),MONTH(A1)+13,1),DATE(YEA R
(A1),MONTH(A1)+7,1))

But this requires a manual insert of Y in a renewal cell
and has no
regard to renewal being within the time period.

There are a number of ways that could be addressed but
it may be best
to decide "by human" whether a late renewal will be
allowed.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax
and Arguments)
available free to good homes.
"Wayne" wrote in message
...
Thanks! That part works great. I have another
question...I want to expand upon the original !

If the contract is re-newed within 60 days of the
expire
date, the six month term is valid from for the time
period as below.
A3_jAN 16 00 Actual Renew date
B3_MAR 01 00 Old Expire Date
C3_OCT 01 00 New Valid to Date

-----Original Message-----
How about:

=DATE(YEAR(A1),MONTH(A1)+7,1)

Where A1 contained the original date.

Wayne wrote:

I have a present date that when entered should
bring up
an expiration date which is the first (1st) day of
the
sixth (6th) month "after the end" of the month that
the
contract originated in. Any thoughts!?

--

Dave Peterson

.



.

Hi... I would like to clarify the label's as I am
somewhat confused with what is below. First, there is the
Renew date or Issue date // then the Start or Actual
date // then the Expire or Valid To date. I prefer
the "Issue Date", "Start Date", and "Valid To" date.

The "Issue Date" may be any date up to 60 days before the
previous "Valid To" date.. if so, then it is valid for 6
months( the 1st day of the 7th following month ) from the
previous "Valid To" date.

THe contract is valid for a full 6 month period from
the "Start Date"( 1st day of the 7th following month ).

I won't bother you any more after this. You have
provided me with much welcome info, and I do appreciate!

Hope you have a good New Year....

-----Original Message-----
Hi Wayne!

Maybe the best approach is to have a date for extension
notice to be
given in A2 and then to may Y calculable:

A1:
Starting date
A2:
Renewal notice
B1:
=IF(A2="","",IF(A2DATE(YEAR(A1),MONTH(A1)+7,1)-
60,"Y",""))
C1:
=IF(B1="Y",DATE(YEAR(A1),MONTH(A1)+13,1),DATE(YEA R
(A1),MONTH(A1)+7,1))

If no notice is given, then A2 is empty and B1 will
return "" which
gives "normal" expiry from C1
If notice is given, then if it's not less than 60 days
from expiry, B1
will return Y and C1 will return "extended" expiry
If notice is given, then if it is less than 60 days from
expiry, B1
will return "" and C1 will return "normal" expiry

But I can do away with the B1 formula using:

C1:
=IF(A2="",DATE(YEAR(A1),MONTH(A1)+7,1),IF(A2DATE (YEAR
(A1),MONTH(A1)+7
,1)-60,DATE(YEAR(A1),MONTH(A1)+13,1),DATE(YEAR(A1),MON TH
(A1)+7,1)))

I can't say I like that approach.

It's not been thoroughly tested so give it a good trial.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax
and Arguments)
available free to good homes.
"Wayne" wrote in message
...
Thankyou again... I guess there are only two ways of
renewing. Early within 60 days; or on time or late.
What I am after is an un-arguable "valid to date",
arrived at from input of "actual" and "old expire"
dates.
If early or "ON" time, then from the 1st of the
following
month to the 1st of the seventh following month ( 6
months );or "ON" time and late, from the 1st of the
following month to the 1st of the seventh following
month
( 6 months ). Now I'm getting confused!

-----Original Message-----
Hi Wayne!

How about something like:

=IF(B1="Y",DATE(YEAR(A1),MONTH(A1)+13,1),DATE(YEA R
(A1),MONTH(A1)+7,1))

But this requires a manual insert of Y in a renewal
cell
and has no
regard to renewal being within the time period.

There are a number of ways that could be addressed but
it may be best
to decide "by human" whether a late renewal will be
allowed.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax
and Arguments)
available free to good homes.
"Wayne" wrote in message
...
Thanks! That part works great. I have another
question...I want to expand upon the original !

If the contract is re-newed within 60 days of the
expire
date, the six month term is valid from for the time
period as below.
A3_jAN 16 00 Actual Renew date
B3_MAR 01 00 Old Expire Date
C3_OCT 01 00 New Valid to Date

-----Original Message-----
How about:

=DATE(YEAR(A1),MONTH(A1)+7,1)

Where A1 contained the original date.

Wayne wrote:

I have a present date that when entered should
bring up
an expiration date which is the first (1st) day
of
the
sixth (6th) month "after the end" of the month
that
the
contract originated in. Any thoughts!?

--

Dave Peterson

.



.



.

Hi Wayne!

Just a further clarification needed.

Option to renew can be exercised between 0 and 60 days before the
valid to date.
What happens if option is served before that time?
What happens if it is served after that time?

Usually with leases etc, notice to renew have to be served not less
than n days before expiry and not later than the expiry. You seem to
have something a bit different.

There's no trouble with posting clarifications or further questions to
these groups.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

Hi Norman!
Ok! I'll be a bit more specific! This contract is really
a government issued licence, that must be validated every
six months with a medical examination.
1. It can be re-newed 0 - 60 days ahead of the valid to
date, applying the 6 month extension to the previous
period.
2. One would never personally renew before that 60 day
grace period!
3. If the medical re-examination date is after the
previous "Valid To" date, then the 6 month period starts
from the date of examination.

Label's: "Issue Date", "Exam Date", and "Valid To Date"

Does that help?

-----Original Message-----
Hi Wayne!

Just a further clarification needed.

Option to renew can be exercised between 0 and 60 days
before the
valid to date.
What happens if option is served before that time?
What happens if it is served after that time?

Usually with leases etc, notice to renew have to be
served not less
than n days before expiry and not later than the expiry.
You seem to
have something a bit different.

There's no trouble with posting clarifications or
further questions to
these groups.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax
and Arguments)
available free to good homes.


.