LEAP YEAR Validation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How do you validate a cell so it will only accept february 29th,yyyy if the year behind has a leap year in it?

For example:I do not want C2 to accept the date february 29,2003 but it can accept Febraury 29, 2004.

A B C D

1

2


Thank you
CTInt04
 
Hi CTInt04

Say you have:-
29-02-2004 in cell A1
29-02-2003 in cell A2

then the formula
=ISNUMBER(A1) in cell B1 will return TRUE
=ISNUMBER(A2) in cell B2 will return FALSE

So that is one way to check for the existence of a valid date. Note that
cells A1 and A2 should be formatted as dates.

--
XL2002
Regards

William

(e-mail address removed)

| How do you validate a cell so it will only accept february 29th,yyyy if
the year behind has a leap year in it?
|
| For example:I do not want C2 to accept the date february 29,2003 but it
can accept Febraury 29, 2004.
|
| A B C D
|
| 1
|
| 2
|
|
| Thank you
| CTInt04
|
|
|
|
|
|
|
|
|
|
 
Hi CTInt04

Say you have:-
29-02-2004 in cell A1
29-02-2003 in cell A2

then the formula
=ISNUMBER(A1) in cell B1 will return TRUE
=ISNUMBER(A2) in cell B2 will return FALSE

So that is one way to check for the existence of a valid date. Note that
cells A1 and A2 should be formatted as dates.


--
XL2002
Regards

William

(e-mail address removed)

| How do you validate a cell so it will only accept february 29th,yyyy if
the year behind has a leap year in it?
|
| For example:I do not want C2 to accept the date february 29,2003 but it
can accept Febraury 29, 2004.
|
| A B C D
|
| 1
|
| 2
|
|
| Thank you
| CTInt04
|
|
|
|
|
|
|
|
|
|
 
How do you validate a cell so it will only accept february 29th,yyyy if
the year behind has a leap year in it?
For example:I do not want C2 to accept the date february 29,2003 but it
Click to expand...
can accept Febraury 29, 2004.

Put the following in the worksheet's code module.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$C$2" Then Exit Sub
If IsDate(Target.Value) = False Then GoTo Skip
If Month(Target.Value) <> 2 Then GoTo Skip
If Day(Target.Value) <> 29 Then GoTo Skip
If Month(Target.Value + 1) <> 3 Then GoTo Skip
Exit Sub

Skip:
Target.Value = ""
End Sub

HTH,
Merjet
 
Thanks

merjet said:
the year behind has a leap year in it?
can accept Febraury 29, 2004.

Put the following in the worksheet's code module.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$C$2" Then Exit Sub
If IsDate(Target.Value) = False Then GoTo Skip
If Month(Target.Value) <> 2 Then GoTo Skip
If Day(Target.Value) <> 29 Then GoTo Skip
If Month(Target.Value + 1) <> 3 Then GoTo Skip
Exit Sub

Skip:
Target.Value = ""
End Sub

HTH,
Merjet
Click to expand...
 
Back
Top