asked on

isDate function

I want to validate the year function...if i want to validate the date enter by a user i will use isDate funtion but how if only year..
this is my script........

If Target.Address = "$J$28" Then
If Sheet1.Range("J28").Value <> Empty Then
If Right(Sheet1.Range("J28"), 4) <= 1999 Then
MsgBox "Tahun taksiran mesti >= 2000", , "Borang CP204"
Sheet1.Range("J28") = ""
Sheet1.Range("J28").Activate

ElseIf Len(Sheet1.Range("J28")) = 4 Then
Sheet1.Range("J28") = Mid((Sheet1.Range("J28")), 1, 4)
ElseIf Len(Sheet1.Range("J28")) < 4 Then
MsgBox "Tahun yang dimasukkan tidak sah", , "Borang CP204"
Sheet1.Range("J28").Activate
Sheet1.Range("J28") = ""

ElseIf Len(Sheet1.Range("J28")) = 4 Then
If Not isDate(Sheet1.Range("J28")) Then
MsgBox "Tahun yang dimasukkan tidak sah", , "Borang CP204"
Sheet1.Range("J28").Activate
Sheet1.Range("J28") = ""
Else
TarikhBayar = Sheet1.Range("J28")
If Sheet1.Range("L32") <> "" Then
If IsDate(Sheet1.Range("L32")) Then
TempohHingga = Sheet1.Range("L32")
If TarikhBayar > TempohHingga Then
MsgBox "Tempoh awal perakaunan mesti < dari tempoh akhir perakaunan", , "Borang CP204"
Sheet1.Range("J28") = ""
Sheet1.Range("J28").Activate
End If
End If
End If

End If
End If
End If
End If

can u modify this code?i need a code that can validate the year by not using the isDate....is there any function to check the year...

Computron

Are you using the full year in the cell ? as in 2005

SOLUTION

Computron

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

inzhagi

ASKER

The J28 will be enter as a year..............for example 2000 or 2001..it should check year
I want to disable user from entering character....only numbers but limited to 4 digits(means its a year format)

ASKER CERTIFIED SOLUTION

jimbobmcgee

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jimbobmcgee

Although, you'd be better off using IsDate(), along the following lines:

bIsValid = IsDate("1/1/" & myValue)

Why can't you use IsDate()?

J.

cjard

can you use a DTPicker?

Microsoft Windows Common Controls 2 if i recall correctly

jimbobmcgee

I still think, if you want to validate only a year, you should append a dummy day and month (like 1/1/) and test it for IsDate compliance. If it has to be a four-digit year, then combine the checks, although this may throw some exceptions. Try the following:

bIsValid = (IsDate("1/1/" & myValue) And (Len(myValue) = 4))

Take the following examples:

myValue "1/1/" & myValue IsDate("1/1/" & myValue) (Len(myValue) = 4) bIsValid
---------- --------------------- ----------------------------- ------------------------ ----------
124 1/1/124 True False False
afb24 1/1/afb24 False False False
1999 1/1/1999 True True True
88 1/1/88 True False False
1111 1/1/1111 True True True

If you can narrow it to a start range, then your validation becomes much easier; if, for example there is no way your year will be less than 1985, you could add a further check:

bIsValid = (IsDate("1/1/" & myValue) And (Len(myValue) = 4) And (myValue >= 1985)

It should be possible to see where to go from here...

J.