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

Thanks Sandy,
Im using the following format "dd-mmm-yy"
--
kk


"Sandy Mann" wrote:

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

I'm not the best VBA programmer - that is why I was hoping that someone with
more skills would jump in - but try this code:

Option Explicit
Sub TestDate()
Dim InDate As String
Dim Idate As Long
Dim EDate As Long
Dim Ans As Integer
Dim eEDate As Long
Dim NexDate As Long
Dim ExDate As String
Dim EYear As Long
Dim EMonth As Long
Dim LastRow As Long

InDateInput:
On Error GoTo 0
InDate = _
InputBox("Please enter the date of the " & _
"Test in the format d-mmm-yy", "Test Date")
'Change the dd/mm/yy to the date system you use

On Error Resume Next
If IsError(DateValue(InDate)) Then
MsgBox "I don't recognise that as a date" & vbLf & _
"Please try again"
GoTo InDateInput
End If

Idate = DateValue(InDate)

Ans = MsgBox("Test Date was: " & _
Format(Idate, "d-mmm-yy") & vbLf & _
"Is That correct?", vbYesNo, "Test Date")
'Change the Format to the date format that you use

If Ans = 7 Then GoTo InDateInput

ExDateInput:
On Error GoTo 0
ExDate = _
InputBox("Please enter the date of the last " & _
"Expiry in the format dd-mmm-yy", "Expiry Date")
'Change the dd/mm/yy to the date system you use
On Error Resume Next
If IsError(DateValue(ExDate)) Then
MsgBox "I don't recognise that as a date" & vbLf & _
"Please try again"
GoTo ExDateInput
End If

EDate = DateValue(ExDate)

Ans = MsgBox("Last Expiry Date is: " _
& Format(EDate, "d-mmm-yy") & vbLf & _
"Is That correct?", vbYesNo, "Expiry Date")
'Change the Format to the date format that you use
If Ans = 7 Then GoTo ExDateInput

EYear = Year(EDate): EMonth = Month(EDate) - 2

eEDate = DateValue(1 & "/" & EMonth & "/" & EYear)

If Idate EDate Then
MsgBox "There is a mistake in one of the dates" & vbLf & _
"Please start again", , "Date Error!"
GoTo InDateInput
End If

If eEDate = Idate Then
MsgBox "You have taken the Test too early", _
, "Test taken too soon"
Exit Sub
End If

EMonth = Month(EDate) + 7
If EMonth 12 Then
EMonth = EMonth - 12
EYear = EYear + 1
End If

NexDate = DateValue(1 & "/" & EMonth & "/" & EYear)
NexDate = NexDate - 1

MsgBox "New Expiry Date is: " _
& Format(NexDate, "d-mmm-yy"), , "New Expiry Date"

Ans = MsgBox("Enter the new dates in the Spreadsheet?", _
vbYesNo, "Update Spreadsheet")

If Ans = 6 Then
With ActiveSheet
Columns("A:B").ColumnWidth = 13
Columns("A:B").NumberFormat = "d-mmm-yy"
End With

Cells(1, 1).Value = "Test Date"
Cells(1, 2).Value = "Expiry Date"

LastRow = Cells(Rows.Count, 2).End(xlUp).Row + 1

Cells(LastRow, 1).Value = Idate
Cells(LastRow, 2).Value = NexDate
End If

End Sub

Note that because the focus is on the *Yes* button just pressing *Enter*
will accept the option and although it says to enter the date in the format
"d-mmm-yy" it also accepts "d/mm/yy"


--
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
...
Thanks Sandy,
Im using the following format "dd-mmm-yy"
--
kk


"Sandy Mann" wrote:

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

Thanks fir that Sandy,
I shall give it a try and let you know how I get on.
--
kk


"Sandy Mann" wrote:

I'm not the best VBA programmer - that is why I was hoping that someone with
more skills would jump in - but try this code:

Option Explicit
Sub TestDate()
Dim InDate As String
Dim Idate As Long
Dim EDate As Long
Dim Ans As Integer
Dim eEDate As Long
Dim NexDate As Long
Dim ExDate As String
Dim EYear As Long
Dim EMonth As Long
Dim LastRow As Long

InDateInput:
On Error GoTo 0
InDate = _
InputBox("Please enter the date of the " & _
"Test in the format d-mmm-yy", "Test Date")
'Change the dd/mm/yy to the date system you use

On Error Resume Next
If IsError(DateValue(InDate)) Then
MsgBox "I don't recognise that as a date" & vbLf & _
"Please try again"
GoTo InDateInput
End If

Idate = DateValue(InDate)

Ans = MsgBox("Test Date was: " & _
Format(Idate, "d-mmm-yy") & vbLf & _
"Is That correct?", vbYesNo, "Test Date")
'Change the Format to the date format that you use

If Ans = 7 Then GoTo InDateInput

ExDateInput:
On Error GoTo 0
ExDate = _
InputBox("Please enter the date of the last " & _
"Expiry in the format dd-mmm-yy", "Expiry Date")
'Change the dd/mm/yy to the date system you use
On Error Resume Next
If IsError(DateValue(ExDate)) Then
MsgBox "I don't recognise that as a date" & vbLf & _
"Please try again"
GoTo ExDateInput
End If

EDate = DateValue(ExDate)

Ans = MsgBox("Last Expiry Date is: " _
& Format(EDate, "d-mmm-yy") & vbLf & _
"Is That correct?", vbYesNo, "Expiry Date")
'Change the Format to the date format that you use
If Ans = 7 Then GoTo ExDateInput

EYear = Year(EDate): EMonth = Month(EDate) - 2

eEDate = DateValue(1 & "/" & EMonth & "/" & EYear)

If Idate EDate Then
MsgBox "There is a mistake in one of the dates" & vbLf & _
"Please start again", , "Date Error!"
GoTo InDateInput
End If

If eEDate = Idate Then
MsgBox "You have taken the Test too early", _
, "Test taken too soon"
Exit Sub
End If

EMonth = Month(EDate) + 7
If EMonth 12 Then
EMonth = EMonth - 12
EYear = EYear + 1
End If

NexDate = DateValue(1 & "/" & EMonth & "/" & EYear)
NexDate = NexDate - 1

MsgBox "New Expiry Date is: " _
& Format(NexDate, "d-mmm-yy"), , "New Expiry Date"

Ans = MsgBox("Enter the new dates in the Spreadsheet?", _
vbYesNo, "Update Spreadsheet")

If Ans = 6 Then
With ActiveSheet
Columns("A:B").ColumnWidth = 13
Columns("A:B").NumberFormat = "d-mmm-yy"
End With

Cells(1, 1).Value = "Test Date"
Cells(1, 2).Value = "Expiry Date"

LastRow = Cells(Rows.Count, 2).End(xlUp).Row + 1

Cells(LastRow, 1).Value = Idate
Cells(LastRow, 2).Value = NexDate
End If

End Sub

Note that because the focus is on the *Yes* button just pressing *Enter*
will accept the option and although it says to enter the date in the format
"d-mmm-yy" it also accepts "d/mm/yy"


--
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
...
Thanks Sandy,
Im using the following format "dd-mmm-yy"
--
kk


"Sandy Mann" wrote:

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

I cant get the code to work, but I think I can work with the formula.
Thanks very much for your help.
--
kk


"kk" wrote:

Thanks fir that Sandy,
I shall give it a try and let you know how I get on.
--
kk


"Sandy Mann" wrote:

I'm not the best VBA programmer - that is why I was hoping that someone with
more skills would jump in - but try this code:

Option Explicit
Sub TestDate()
Dim InDate As String
Dim Idate As Long
Dim EDate As Long
Dim Ans As Integer
Dim eEDate As Long
Dim NexDate As Long
Dim ExDate As String
Dim EYear As Long
Dim EMonth As Long
Dim LastRow As Long

InDateInput:
On Error GoTo 0
InDate = _
InputBox("Please enter the date of the " & _
"Test in the format d-mmm-yy", "Test Date")
'Change the dd/mm/yy to the date system you use

On Error Resume Next
If IsError(DateValue(InDate)) Then
MsgBox "I don't recognise that as a date" & vbLf & _
"Please try again"
GoTo InDateInput
End If

Idate = DateValue(InDate)

Ans = MsgBox("Test Date was: " & _
Format(Idate, "d-mmm-yy") & vbLf & _
"Is That correct?", vbYesNo, "Test Date")
'Change the Format to the date format that you use

If Ans = 7 Then GoTo InDateInput

ExDateInput:
On Error GoTo 0
ExDate = _
InputBox("Please enter the date of the last " & _
"Expiry in the format dd-mmm-yy", "Expiry Date")
'Change the dd/mm/yy to the date system you use
On Error Resume Next
If IsError(DateValue(ExDate)) Then
MsgBox "I don't recognise that as a date" & vbLf & _
"Please try again"
GoTo ExDateInput
End If

EDate = DateValue(ExDate)

Ans = MsgBox("Last Expiry Date is: " _
& Format(EDate, "d-mmm-yy") & vbLf & _
"Is That correct?", vbYesNo, "Expiry Date")
'Change the Format to the date format that you use
If Ans = 7 Then GoTo ExDateInput

EYear = Year(EDate): EMonth = Month(EDate) - 2

eEDate = DateValue(1 & "/" & EMonth & "/" & EYear)

If Idate EDate Then
MsgBox "There is a mistake in one of the dates" & vbLf & _
"Please start again", , "Date Error!"
GoTo InDateInput
End If

If eEDate = Idate Then
MsgBox "You have taken the Test too early", _
, "Test taken too soon"
Exit Sub
End If

EMonth = Month(EDate) + 7
If EMonth 12 Then
EMonth = EMonth - 12
EYear = EYear + 1
End If

NexDate = DateValue(1 & "/" & EMonth & "/" & EYear)
NexDate = NexDate - 1

MsgBox "New Expiry Date is: " _
& Format(NexDate, "d-mmm-yy"), , "New Expiry Date"

Ans = MsgBox("Enter the new dates in the Spreadsheet?", _
vbYesNo, "Update Spreadsheet")

If Ans = 6 Then
With ActiveSheet
Columns("A:B").ColumnWidth = 13
Columns("A:B").NumberFormat = "d-mmm-yy"
End With

Cells(1, 1).Value = "Test Date"
Cells(1, 2).Value = "Expiry Date"

LastRow = Cells(Rows.Count, 2).End(xlUp).Row + 1

Cells(LastRow, 1).Value = Idate
Cells(LastRow, 2).Value = NexDate
End If

End Sub

Note that because the focus is on the *Yes* button just pressing *Enter*
will accept the option and although it says to enter the date in the format
"d-mmm-yy" it also accepts "d/mm/yy"


--
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
...
Thanks Sandy,
Im using the following format "dd-mmm-yy"
--
kk


"Sandy Mann" wrote:

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

What does *I cant get the code to work* mean? Nothing happens? Gives
incorrect results?

--
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
...
I cant get the code to work, but I think I can work with the formula.
Thanks very much for your help.
--
kk


"kk" wrote:

Thanks fir that Sandy,
I shall give it a try and let you know how I get on.
--
kk


"Sandy Mann" wrote:

I'm not the best VBA programmer - that is why I was hoping that someone
with
more skills would jump in - but try this code:

Option Explicit
Sub TestDate()
Dim InDate As String
Dim Idate As Long
Dim EDate As Long
Dim Ans As Integer
Dim eEDate As Long
Dim NexDate As Long
Dim ExDate As String
Dim EYear As Long
Dim EMonth As Long
Dim LastRow As Long

InDateInput:
On Error GoTo 0
InDate = _
InputBox("Please enter the date of the " & _
"Test in the format d-mmm-yy", "Test Date")
'Change the dd/mm/yy to the date system you use

On Error Resume Next
If IsError(DateValue(InDate)) Then
MsgBox "I don't recognise that as a date" & vbLf & _
"Please try again"
GoTo InDateInput
End If

Idate = DateValue(InDate)

Ans = MsgBox("Test Date was: " & _
Format(Idate, "d-mmm-yy") & vbLf & _
"Is That correct?", vbYesNo, "Test Date")
'Change the Format to the date format that you use

If Ans = 7 Then GoTo InDateInput

ExDateInput:
On Error GoTo 0
ExDate = _
InputBox("Please enter the date of the last " & _
"Expiry in the format dd-mmm-yy", "Expiry Date")
'Change the dd/mm/yy to the date system you use
On Error Resume Next
If IsError(DateValue(ExDate)) Then
MsgBox "I don't recognise that as a date" & vbLf & _
"Please try again"
GoTo ExDateInput
End If

EDate = DateValue(ExDate)

Ans = MsgBox("Last Expiry Date is: " _
& Format(EDate, "d-mmm-yy") & vbLf & _
"Is That correct?", vbYesNo, "Expiry Date")
'Change the Format to the date format that you use
If Ans = 7 Then GoTo ExDateInput

EYear = Year(EDate): EMonth = Month(EDate) - 2

eEDate = DateValue(1 & "/" & EMonth & "/" & EYear)

If Idate EDate Then
MsgBox "There is a mistake in one of the dates" & vbLf & _
"Please start again", , "Date Error!"
GoTo InDateInput
End If

If eEDate = Idate Then
MsgBox "You have taken the Test too early", _
, "Test taken too soon"
Exit Sub
End If

EMonth = Month(EDate) + 7
If EMonth 12 Then
EMonth = EMonth - 12
EYear = EYear + 1
End If

NexDate = DateValue(1 & "/" & EMonth & "/" & EYear)
NexDate = NexDate - 1

MsgBox "New Expiry Date is: " _
& Format(NexDate, "d-mmm-yy"), , "New Expiry Date"

Ans = MsgBox("Enter the new dates in the Spreadsheet?", _
vbYesNo, "Update Spreadsheet")

If Ans = 6 Then
With ActiveSheet
Columns("A:B").ColumnWidth = 13
Columns("A:B").NumberFormat = "d-mmm-yy"
End With

Cells(1, 1).Value = "Test Date"
Cells(1, 2).Value = "Expiry Date"

LastRow = Cells(Rows.Count, 2).End(xlUp).Row + 1

Cells(LastRow, 1).Value = Idate
Cells(LastRow, 2).Value = NexDate
End If

End Sub

Note that because the focus is on the *Yes* button just pressing
*Enter*
will accept the option and although it says to enter the date in the
format
"d-mmm-yy" it also accepts "d/mm/yy"


--
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
...
Thanks Sandy,
Im using the following format "dd-mmm-yy"
--
kk


"Sandy Mann" wrote:

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

Nothing happens.
Maybe I have placed it in the wrong place!
--
kk


"Sandy Mann" wrote:

What does *I cant get the code to work* mean? Nothing happens? Gives
incorrect results?

--
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
...
I cant get the code to work, but I think I can work with the formula.
Thanks very much for your help.
--
kk


"kk" wrote:

Thanks fir that Sandy,
I shall give it a try and let you know how I get on.
--
kk


"Sandy Mann" wrote:

I'm not the best VBA programmer - that is why I was hoping that someone
with
more skills would jump in - but try this code:

Option Explicit
Sub TestDate()
Dim InDate As String
Dim Idate As Long
Dim EDate As Long
Dim Ans As Integer
Dim eEDate As Long
Dim NexDate As Long
Dim ExDate As String
Dim EYear As Long
Dim EMonth As Long
Dim LastRow As Long

InDateInput:
On Error GoTo 0
InDate = _
InputBox("Please enter the date of the " & _
"Test in the format d-mmm-yy", "Test Date")
'Change the dd/mm/yy to the date system you use

On Error Resume Next
If IsError(DateValue(InDate)) Then
MsgBox "I don't recognise that as a date" & vbLf & _
"Please try again"
GoTo InDateInput
End If

Idate = DateValue(InDate)

Ans = MsgBox("Test Date was: " & _
Format(Idate, "d-mmm-yy") & vbLf & _
"Is That correct?", vbYesNo, "Test Date")
'Change the Format to the date format that you use

If Ans = 7 Then GoTo InDateInput

ExDateInput:
On Error GoTo 0
ExDate = _
InputBox("Please enter the date of the last " & _
"Expiry in the format dd-mmm-yy", "Expiry Date")
'Change the dd/mm/yy to the date system you use
On Error Resume Next
If IsError(DateValue(ExDate)) Then
MsgBox "I don't recognise that as a date" & vbLf & _
"Please try again"
GoTo ExDateInput
End If

EDate = DateValue(ExDate)

Ans = MsgBox("Last Expiry Date is: " _
& Format(EDate, "d-mmm-yy") & vbLf & _
"Is That correct?", vbYesNo, "Expiry Date")
'Change the Format to the date format that you use
If Ans = 7 Then GoTo ExDateInput

EYear = Year(EDate): EMonth = Month(EDate) - 2

eEDate = DateValue(1 & "/" & EMonth & "/" & EYear)

If Idate EDate Then
MsgBox "There is a mistake in one of the dates" & vbLf & _
"Please start again", , "Date Error!"
GoTo InDateInput
End If

If eEDate = Idate Then
MsgBox "You have taken the Test too early", _
, "Test taken too soon"
Exit Sub
End If

EMonth = Month(EDate) + 7
If EMonth 12 Then
EMonth = EMonth - 12
EYear = EYear + 1
End If

NexDate = DateValue(1 & "/" & EMonth & "/" & EYear)
NexDate = NexDate - 1

MsgBox "New Expiry Date is: " _
& Format(NexDate, "d-mmm-yy"), , "New Expiry Date"

Ans = MsgBox("Enter the new dates in the Spreadsheet?", _
vbYesNo, "Update Spreadsheet")

If Ans = 6 Then
With ActiveSheet
Columns("A:B").ColumnWidth = 13
Columns("A:B").NumberFormat = "d-mmm-yy"
End With

Cells(1, 1).Value = "Test Date"
Cells(1, 2).Value = "Expiry Date"

LastRow = Cells(Rows.Count, 2).End(xlUp).Row + 1

Cells(LastRow, 1).Value = Idate
Cells(LastRow, 2).Value = NexDate
End If

End Sub

Note that because the focus is on the *Yes* button just pressing
*Enter*
will accept the option and although it says to enter the date in the
format
"d-mmm-yy" it also accepts "d/mm/yy"


--
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
...
Thanks Sandy,
Im using the following format "dd-mmm-yy"
--
kk


"Sandy Mann" wrote:

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

Hi kk,

First of all you must have Macros enabled to run the code:

Tools Macro Security select *Medium* or *Low,* (but Low is not
recommended)

Right-click on the Sheet Tab and select *View Code.* Select Insert Module
(or press and hold Alt while you press I and then M)

This will insert a new Module 1. Copy and paste the code into this module.

Close and reopen the workbook and you sould be asked to Eable Macros.

Select: Tools Options Macros and either select the Macro and click on
Run or

Click on the *Options* button instead of the Macro and then in the *Macro
Options* dialog box enter the shortcut key that you want to run the Macro
from and click on OK then the *Cancel* button on the Macro dialog box.

The Macro will now run if you press had hold the Ctrl key while you press
the shortcut key.

Note: The Shortcut key is case sensitive and don't pick a key that you want
to use for some other function, (ie if you select c then ctrl + c will no
longer copy data).

Post back if you are still having trouble.


--
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
...
Nothing happens.
Maybe I have placed it in the wrong place!
--
kk


"Sandy Mann" wrote:

What does *I cant get the code to work* mean? Nothing happens? Gives
incorrect results?

--
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
...
I cant get the code to work, but I think I can work with the formula.
Thanks very much for your help.
--
kk


"kk" wrote:

Thanks fir that Sandy,
I shall give it a try and let you know how I get on.
--
kk


"Sandy Mann" wrote:

I'm not the best VBA programmer - that is why I was hoping that
someone
with
more skills would jump in - but try this code:

Option Explicit
Sub TestDate()
Dim InDate As String
Dim Idate As Long
Dim EDate As Long
Dim Ans As Integer
Dim eEDate As Long
Dim NexDate As Long
Dim ExDate As String
Dim EYear As Long
Dim EMonth As Long
Dim LastRow As Long

InDateInput:
On Error GoTo 0
InDate = _
InputBox("Please enter the date of the " & _
"Test in the format d-mmm-yy", "Test Date")
'Change the dd/mm/yy to the date system you use

On Error Resume Next
If IsError(DateValue(InDate)) Then
MsgBox "I don't recognise that as a date" & vbLf & _
"Please try again"
GoTo InDateInput
End If

Idate = DateValue(InDate)

Ans = MsgBox("Test Date was: " & _
Format(Idate, "d-mmm-yy") & vbLf & _
"Is That correct?", vbYesNo, "Test Date")
'Change the Format to the date format that you use

If Ans = 7 Then GoTo InDateInput

ExDateInput:
On Error GoTo 0
ExDate = _
InputBox("Please enter the date of the last " & _
"Expiry in the format dd-mmm-yy", "Expiry Date")
'Change the dd/mm/yy to the date system you use
On Error Resume Next
If IsError(DateValue(ExDate)) Then
MsgBox "I don't recognise that as a date" & vbLf & _
"Please try again"
GoTo ExDateInput
End If

EDate = DateValue(ExDate)

Ans = MsgBox("Last Expiry Date is: " _
& Format(EDate, "d-mmm-yy") & vbLf & _
"Is That correct?", vbYesNo, "Expiry Date")
'Change the Format to the date format that you use
If Ans = 7 Then GoTo ExDateInput

EYear = Year(EDate): EMonth = Month(EDate) - 2

eEDate = DateValue(1 & "/" & EMonth & "/" & EYear)

If Idate EDate Then
MsgBox "There is a mistake in one of the dates" & vbLf & _
"Please start again", , "Date Error!"
GoTo InDateInput
End If

If eEDate = Idate Then
MsgBox "You have taken the Test too early", _
, "Test taken too soon"
Exit Sub
End If

EMonth = Month(EDate) + 7
If EMonth 12 Then
EMonth = EMonth - 12
EYear = EYear + 1
End If

NexDate = DateValue(1 & "/" & EMonth & "/" & EYear)
NexDate = NexDate - 1

MsgBox "New Expiry Date is: " _
& Format(NexDate, "d-mmm-yy"), , "New Expiry Date"

Ans = MsgBox("Enter the new dates in the Spreadsheet?", _
vbYesNo, "Update Spreadsheet")

If Ans = 6 Then
With ActiveSheet
Columns("A:B").ColumnWidth = 13
Columns("A:B").NumberFormat = "d-mmm-yy"
End With

Cells(1, 1).Value = "Test Date"
Cells(1, 2).Value = "Expiry Date"

LastRow = Cells(Rows.Count, 2).End(xlUp).Row + 1

Cells(LastRow, 1).Value = Idate
Cells(LastRow, 2).Value = NexDate
End If

End Sub

Note that because the focus is on the *Yes* button just pressing
*Enter*
will accept the option and although it says to enter the date in the
format
"d-mmm-yy" it also accepts "d/mm/yy"


--
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
...
Thanks Sandy,
Im using the following format "dd-mmm-yy"
--
kk


"Sandy Mann" wrote:

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

Thanks Sandy,
I will give it a go and let you know how I get on.
--
kk


"Sandy Mann" wrote:

Hi kk,

First of all you must have Macros enabled to run the code:

Tools Macro Security select *Medium* or *Low,* (but Low is not
recommended)

Right-click on the Sheet Tab and select *View Code.* Select Insert Module
(or press and hold Alt while you press I and then M)

This will insert a new Module 1. Copy and paste the code into this module.

Close and reopen the workbook and you sould be asked to Eable Macros.

Select: Tools Options Macros and either select the Macro and click on
Run or

Click on the *Options* button instead of the Macro and then in the *Macro
Options* dialog box enter the shortcut key that you want to run the Macro
from and click on OK then the *Cancel* button on the Macro dialog box.

The Macro will now run if you press had hold the Ctrl key while you press
the shortcut key.

Note: The Shortcut key is case sensitive and don't pick a key that you want
to use for some other function, (ie if you select c then ctrl + c will no
longer copy data).

Post back if you are still having trouble.


--
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
...
Nothing happens.
Maybe I have placed it in the wrong place!
--
kk


"Sandy Mann" wrote:

What does *I cant get the code to work* mean? Nothing happens? Gives
incorrect results?

--
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
...
I cant get the code to work, but I think I can work with the formula.
Thanks very much for your help.
--
kk


"kk" wrote:

Thanks fir that Sandy,
I shall give it a try and let you know how I get on.
--
kk


"Sandy Mann" wrote:

I'm not the best VBA programmer - that is why I was hoping that
someone
with
more skills would jump in - but try this code:

Option Explicit
Sub TestDate()
Dim InDate As String
Dim Idate As Long
Dim EDate As Long
Dim Ans As Integer
Dim eEDate As Long
Dim NexDate As Long
Dim ExDate As String
Dim EYear As Long
Dim EMonth As Long
Dim LastRow As Long

InDateInput:
On Error GoTo 0
InDate = _
InputBox("Please enter the date of the " & _
"Test in the format d-mmm-yy", "Test Date")
'Change the dd/mm/yy to the date system you use

On Error Resume Next
If IsError(DateValue(InDate)) Then
MsgBox "I don't recognise that as a date" & vbLf & _
"Please try again"
GoTo InDateInput
End If

Idate = DateValue(InDate)

Ans = MsgBox("Test Date was: " & _
Format(Idate, "d-mmm-yy") & vbLf & _
"Is That correct?", vbYesNo, "Test Date")
'Change the Format to the date format that you use

If Ans = 7 Then GoTo InDateInput

ExDateInput:
On Error GoTo 0
ExDate = _
InputBox("Please enter the date of the last " & _
"Expiry in the format dd-mmm-yy", "Expiry Date")
'Change the dd/mm/yy to the date system you use
On Error Resume Next
If IsError(DateValue(ExDate)) Then
MsgBox "I don't recognise that as a date" & vbLf & _
"Please try again"
GoTo ExDateInput
End If

EDate = DateValue(ExDate)

Ans = MsgBox("Last Expiry Date is: " _
& Format(EDate, "d-mmm-yy") & vbLf & _
"Is That correct?", vbYesNo, "Expiry Date")
'Change the Format to the date format that you use
If Ans = 7 Then GoTo ExDateInput

EYear = Year(EDate): EMonth = Month(EDate) - 2

eEDate = DateValue(1 & "/" & EMonth & "/" & EYear)

If Idate EDate Then
MsgBox "There is a mistake in one of the dates" & vbLf & _
"Please start again", , "Date Error!"
GoTo InDateInput
End If

If eEDate = Idate Then
MsgBox "You have taken the Test too early", _
, "Test taken too soon"
Exit Sub
End If

EMonth = Month(EDate) + 7
If EMonth 12 Then
EMonth = EMonth - 12
EYear = EYear + 1
End If

NexDate = DateValue(1 & "/" & EMonth & "/" & EYear)
NexDate = NexDate - 1

MsgBox "New Expiry Date is: " _
& Format(NexDate, "d-mmm-yy"), , "New Expiry Date"

Ans = MsgBox("Enter the new dates in the Spreadsheet?", _
vbYesNo, "Update Spreadsheet")

If Ans = 6 Then
With ActiveSheet
Columns("A:B").ColumnWidth = 13
Columns("A:B").NumberFormat = "d-mmm-yy"
End With

Cells(1, 1).Value = "Test Date"
Cells(1, 2).Value = "Expiry Date"

LastRow = Cells(Rows.Count, 2).End(xlUp).Row + 1

Cells(LastRow, 1).Value = Idate
Cells(LastRow, 2).Value = NexDate
End If

End Sub

Note that because the focus is on the *Yes* button just pressing
*Enter*
will accept the option and although it says to enter the date in the
format
"d-mmm-yy" it also accepts "d/mm/yy"


--
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
...
Thanks Sandy,
Im using the following format "dd-mmm-yy"
--
kk


"Sandy Mann" wrote:

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