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