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

[Wayne]

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]

How about:

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

Where A1 contained the original date.

 

[Ken Wright]

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)

 

[Wayne]

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

 

[Norman Harker]

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
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Wayne]

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!

 

[Norman Harker]

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(A2<DATE(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(A2<DATE(YEAR(A1),MONTH(A1)+7
,1)-60,DATE(YEAR(A1),MONTH(A1)+13,1),DATE(YEAR(A1),MONTH(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
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Wayne]

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

 

[Norman Harker]

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
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Wayne]

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?

 

[Norman Harker]

Hi Wayne!

I understand the system now!

A1 contains the first issue date
B1 the medical exam date

C1
=IF(B1="",DATE(YEAR(A1),MONTH(A1)+7,1),IF(B1>=DATE(YEAR(A1),MONTH(A1)+
7,1)-60,IF(B1>DATE(YEAR(A1),MONTH(A1)+7,1),DATE(YEAR(B1),MONTH(B1)+7,1
),DATE(YEAR(A1),MONTH(A1)+13,1)),DATE(YEAR(A1),MONTH(A1)+7,1)))

If there is no medical examination the expiry date is untouched at 1st
day of month after six months
If there is an exam within the 60 day period then it's extended 6
months
If there's an exam after the 60 day period then this acts the same as
a new license.
If there's an examination before the beginning of the 60 day period,
it has no impact.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Wayne]

Hi Norman... Works great! Now.... To expand on
this,there is a requirement regarding age that makes re-
issue either 12 months or 6 months. Validity period for
under 40 years is 12 months, and 40 & over years old it
is 6 months. I would have a Birth Date entry somewhere in
the page.

I would also like to be able to have two warnings of
pending renewal date "MEDICAL", 2 months ahead and one
month ahead. The warning would come up in a specified
cell and page of a workbook, as this is part of a 1 year
calendar.