LEAP YEAR Validation

[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

 

[Frank Kabel]

Hi
just enter the date value in a date format and Excel should do the rest

 

[William]

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
|
|
|
|
|
|
|
|
|
|

 

[William]

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
|
|
|
|
|
|
|
|
|
|

 

[Frank Kabel]

Hi
disregard my previous post,, use William's suggestion

 

[merjet]

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.

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

 

[Guest]

Thanks

 

[Guest]

You're right it works
Thanks

Hi
just enter the date value in a date format and Excel should do the rest

 

[Guest]

Thanks

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