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