I shall try to explain!!!
I keep a record of certificate dates. These certificates are valid for six
month only and have to be renewed every time. The certificate end date will
always be at the end of the sixth month, but the start can be at any time at
the first month.
ie if I renewed on the 20th of Jan then the run out date will be 31 July.
Now to complicate things. I can renew a certificate within 3 month of its
expiry date the start date wil still be the same.
ie with the same example...had I renewed within 3 month prior to the end of
Jan, the expiry date will be the same (31 july). I hope that makes sense!!
How can that be written in vb code?
--
kk

kk,

Let me get this straight. I can renew for 6 more months from May 1st to
July 31st, but my start date will still be Jan 20th and my Expiration date
will still be July 31st, thus requiring me to renew AGAIN for 6 more months
after July 31st? Is there a charge to renew? If so, what company do you
work for. I'll be sure that I never require your services. It sounds like
you guys are ripping people off!!! :-D (or <vbg)

Please provide more information. Is Start Date coming from a cell? Is
Renew Date coming from a cell? Are you trying to put Expiration Date in a
cell? Are you trying to create a UDF to return Expiration Date to a cell?
Are you trying to update values in current cells or are you adding rows to a
table with these new values? Please post the code you have so far.

How can that be written in vb code?

I don't know how that can be written in VBA until I know what you are trying
to do.

HTH,

Conan






"kk" wrote in message
...
I shall try to explain!!!
I keep a record of certificate dates. These certificates are valid for six
month only and have to be renewed every time. The certificate end date
will
always be at the end of the sixth month, but the start can be at any time
at
the first month.
ie if I renewed on the 20th of Jan then the run out date will be 31 July.
Now to complicate things. I can renew a certificate within 3 month of its
expiry date the start date wil still be the same.
ie with the same example...had I renewed within 3 month prior to the end
of
Jan, the expiry date will be the same (31 july). I hope that makes sense!!
How can that be written in vb code?
--
kk

Hi Conan,
Ok here we go!!!! I work in the aviation world as a pilot. Every 6 month we
have to do a test (which generates a certificate to say we have completed and
passed this test). I have a form (using excel) which I keep a record of these
dates. Cell "A2"= date test taken, Cell "A2"= test expires (this date will
always be the last date of the month). We have to renew before the expiry
date and that will give us another 6 month. Now, One can elect to renew
before the expiry date, and this can be done within 3 month of that date, but
the new renewal date will always be 6month from the original expiry date. (
I`m getting confused myself!!!!!!)......to give an example:
A B
Start date Expiry date
09-Nov-07 31-May-08

Case 1: If I renew on any day in May then my next renewal date will be
30-Nov-08

Case 2: If I renew on any day between 1st Mar and the end of May then my
next renewal date will be 30-Nov-08.

Case 3: If I let it lapse and renew on any day say in June then my next
renewal date will be 30-Dec-08

Incidentally, no money is charged.....all free!!!!!
I hope that has answered any confusions!

--
kk


"Conan Kelly" wrote:

kk,

Let me get this straight. I can renew for 6 more months from May 1st to
July 31st, but my start date will still be Jan 20th and my Expiration date
will still be July 31st, thus requiring me to renew AGAIN for 6 more months
after July 31st? Is there a charge to renew? If so, what company do you
work for. I'll be sure that I never require your services. It sounds like
you guys are ripping people off!!! :-D (or <vbg)

Please provide more information. Is Start Date coming from a cell? Is
Renew Date coming from a cell? Are you trying to put Expiration Date in a
cell? Are you trying to create a UDF to return Expiration Date to a cell?
Are you trying to update values in current cells or are you adding rows to a
table with these new values? Please post the code you have so far.

How can that be written in vb code?

I don't know how that can be written in VBA until I know what you are trying
to do.

HTH,

Conan






"kk" wrote in message
...
I shall try to explain!!!
I keep a record of certificate dates. These certificates are valid for six
month only and have to be renewed every time. The certificate end date
will
always be at the end of the sixth month, but the start can be at any time
at
the first month.
ie if I renewed on the 20th of Jan then the run out date will be 31 July.
Now to complicate things. I can renew a certificate within 3 month of its
expiry date the start date wil still be the same.
ie with the same example...had I renewed within 3 month prior to the end
of
Jan, the expiry date will be the same (31 july). I hope that makes sense!!
How can that be written in vb code?
--
kk

With the 1st Start Date in A2, and the Expiry Date in B2, the next expiry
date in B3 is:

=IF(A3="","",IF(DATE(YEAR(B2),MONTH(B2)-2,0)+1<=A3,MAX(DATE(YEAR(A3),MONTH(A3)+7,0),DATE(Y EAR(B2),MONTH(B2)+7,0)),"Too
Early"))

For all three of your scenarios

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"kk" wrote in message
...
Hi Conan,
Ok here we go!!!! I work in the aviation world as a pilot. Every 6 month
we
have to do a test (which generates a certificate to say we have completed
and
passed this test). I have a form (using excel) which I keep a record of
these
dates. Cell "A2"= date test taken, Cell "A2"= test expires (this date will
always be the last date of the month). We have to renew before the expiry
date and that will give us another 6 month. Now, One can elect to renew
before the expiry date, and this can be done within 3 month of that date,
but
the new renewal date will always be 6month from the original expiry date.
(
I`m getting confused myself!!!!!!)......to give an example:
A B
Start date Expiry date
09-Nov-07 31-May-08

Case 1: If I renew on any day in May then my next renewal date will be
30-Nov-08

Case 2: If I renew on any day between 1st Mar and the end of May then my
next renewal date will be 30-Nov-08.

Case 3: If I let it lapse and renew on any day say in June then my next
renewal date will be 30-Dec-08

Incidentally, no money is charged.....all free!!!!!
I hope that has answered any confusions!

--
kk


"Conan Kelly" wrote:

kk,

Let me get this straight. I can renew for 6 more months from May 1st to
July 31st, but my start date will still be Jan 20th and my Expiration
date
will still be July 31st, thus requiring me to renew AGAIN for 6 more
months
after July 31st? Is there a charge to renew? If so, what company do you
work for. I'll be sure that I never require your services. It sounds
like
you guys are ripping people off!!! :-D (or <vbg)

Please provide more information. Is Start Date coming from a cell? Is
Renew Date coming from a cell? Are you trying to put Expiration Date in
a
cell? Are you trying to create a UDF to return Expiration Date to a
cell?
Are you trying to update values in current cells or are you adding rows
to a
table with these new values? Please post the code you have so far.

How can that be written in vb code?

I don't know how that can be written in VBA until I know what you are
trying
to do.

HTH,

Conan






"kk" wrote in message
...
I shall try to explain!!!
I keep a record of certificate dates. These certificates are valid for
six
month only and have to be renewed every time. The certificate end date
will
always be at the end of the sixth month, but the start can be at any
time
at
the first month.
ie if I renewed on the 20th of Jan then the run out date will be 31
July.
Now to complicate things. I can renew a certificate within 3 month of
its
expiry date the start date wil still be the same.
ie with the same example...had I renewed within 3 month prior to the
end
of
Jan, the expiry date will be the same (31 july). I hope that makes
sense!!
How can that be written in vb code?
--
kk

Thanks Sandy,
Is there a vb code to do the same?
--
kk


"Sandy Mann" wrote:

With the 1st Start Date in A2, and the Expiry Date in B2, the next expiry
date in B3 is:

=IF(A3="","",IF(DATE(YEAR(B2),MONTH(B2)-2,0)+1<=A3,MAX(DATE(YEAR(A3),MONTH(A3)+7,0),DATE(Y EAR(B2),MONTH(B2)+7,0)),"Too
Early"))

For all three of your scenarios

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"kk" wrote in message
...
Hi Conan,
Ok here we go!!!! I work in the aviation world as a pilot. Every 6 month
we
have to do a test (which generates a certificate to say we have completed
and
passed this test). I have a form (using excel) which I keep a record of
these
dates. Cell "A2"= date test taken, Cell "A2"= test expires (this date will
always be the last date of the month). We have to renew before the expiry
date and that will give us another 6 month. Now, One can elect to renew
before the expiry date, and this can be done within 3 month of that date,
but
the new renewal date will always be 6month from the original expiry date.
(
I`m getting confused myself!!!!!!)......to give an example:
A B
Start date Expiry date
09-Nov-07 31-May-08

Case 1: If I renew on any day in May then my next renewal date will be
30-Nov-08

Case 2: If I renew on any day between 1st Mar and the end of May then my
next renewal date will be 30-Nov-08.

Case 3: If I let it lapse and renew on any day say in June then my next
renewal date will be 30-Dec-08

Incidentally, no money is charged.....all free!!!!!
I hope that has answered any confusions!

--
kk


"Conan Kelly" wrote:

kk,

Let me get this straight. I can renew for 6 more months from May 1st to
July 31st, but my start date will still be Jan 20th and my Expiration
date
will still be July 31st, thus requiring me to renew AGAIN for 6 more
months
after July 31st? Is there a charge to renew? If so, what company do you
work for. I'll be sure that I never require your services. It sounds
like
you guys are ripping people off!!! :-D (or <vbg)

Please provide more information. Is Start Date coming from a cell? Is
Renew Date coming from a cell? Are you trying to put Expiration Date in
a
cell? Are you trying to create a UDF to return Expiration Date to a
cell?
Are you trying to update values in current cells or are you adding rows
to a
table with these new values? Please post the code you have so far.

How can that be written in vb code?

I don't know how that can be written in VBA until I know what you are
trying
to do.

HTH,

Conan






"kk" wrote in message
...
I shall try to explain!!!
I keep a record of certificate dates. These certificates are valid for
six
month only and have to be renewed every time. The certificate end date
will
always be at the end of the sixth month, but the start can be at any
time
at
the first month.
ie if I renewed on the 20th of Jan then the run out date will be 31
July.
Now to complicate things. I can renew a certificate within 3 month of
its
expiry date the start date wil still be the same.
ie with the same example...had I renewed within 3 month prior to the
end
of
Jan, the expiry date will be the same (31 july). I hope that makes
sense!!
How can that be written in vb code?
--
kk

If no one else gives you an answer I will have a look at it tomorrow. What
date system do you use, American or British?

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"kk" wrote in message
...
Thanks Sandy,
Is there a vb code to do the same?
--
kk


"Sandy Mann" wrote:

With the 1st Start Date in A2, and the Expiry Date in B2, the next expiry
date in B3 is:

=IF(A3="","",IF(DATE(YEAR(B2),MONTH(B2)-2,0)+1<=A3,MAX(DATE(YEAR(A3),MONTH(A3)+7,0),DATE(Y EAR(B2),MONTH(B2)+7,0)),"Too
Early"))

For all three of your scenarios

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"kk" wrote in message
...
Hi Conan,
Ok here we go!!!! I work in the aviation world as a pilot. Every 6
month
we
have to do a test (which generates a certificate to say we have
completed
and
passed this test). I have a form (using excel) which I keep a record of
these
dates. Cell "A2"= date test taken, Cell "A2"= test expires (this date
will
always be the last date of the month). We have to renew before the
expiry
date and that will give us another 6 month. Now, One can elect to renew
before the expiry date, and this can be done within 3 month of that
date,
but
the new renewal date will always be 6month from the original expiry
date.
(
I`m getting confused myself!!!!!!)......to give an example:
A B
Start date Expiry date
09-Nov-07 31-May-08

Case 1: If I renew on any day in May then my next renewal date will be
30-Nov-08

Case 2: If I renew on any day between 1st Mar and the end of May then
my
next renewal date will be 30-Nov-08.

Case 3: If I let it lapse and renew on any day say in June then my next
renewal date will be 30-Dec-08

Incidentally, no money is charged.....all free!!!!!
I hope that has answered any confusions!

--
kk


"Conan Kelly" wrote:

kk,

Let me get this straight. I can renew for 6 more months from May 1st
to
July 31st, but my start date will still be Jan 20th and my Expiration
date
will still be July 31st, thus requiring me to renew AGAIN for 6 more
months
after July 31st? Is there a charge to renew? If so, what company do
you
work for. I'll be sure that I never require your services. It sounds
like
you guys are ripping people off!!! :-D (or <vbg)

Please provide more information. Is Start Date coming from a cell?
Is
Renew Date coming from a cell? Are you trying to put Expiration Date
in
a
cell? Are you trying to create a UDF to return Expiration Date to a
cell?
Are you trying to update values in current cells or are you adding
rows
to a
table with these new values? Please post the code you have so far.

How can that be written in vb code?

I don't know how that can be written in VBA until I know what you are
trying
to do.

HTH,

Conan






"kk" wrote in message
...
I shall try to explain!!!
I keep a record of certificate dates. These certificates are valid
for
six
month only and have to be renewed every time. The certificate end
date
will
always be at the end of the sixth month, but the start can be at any
time
at
the first month.
ie if I renewed on the 20th of Jan then the run out date will be 31
July.
Now to complicate things. I can renew a certificate within 3 month
of
its
expiry date the start date wil still be the same.
ie with the same example...had I renewed within 3 month prior to the
end
of
Jan, the expiry date will be the same (31 july). I hope that makes
sense!!
How can that be written in vb code?
--
kk