LEAP YEAR Validation
 

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

LEAP YEAR Validation
 

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

--
Regards
Frank Kabel
Frankfurt, Germany


CTInt04 wrote:
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

LEAP YEAR Validation
 

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



"CTInt04" wrote in message
...
| 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
|
|
|
|
|
|
|
|
|
|

LEAP YEAR Validation
 

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



"CTInt04" wrote in message
...
| 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
|
|
|
|
|
|
|
|
|
|

LEAP YEAR Validation
 

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

--
Regards
Frank Kabel
Frankfurt, Germany


Frank Kabel wrote:
Hi
just enter the date value in a date format and Excel should do the
rest


CTInt04 wrote:
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

LEAP YEAR Validation
 

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

LEAP YEAR Validation
 

Thanks

"William" wrote:

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



"CTInt04" wrote in message
...
| 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
|
|
|
|
|
|
|
|
|
|

LEAP YEAR Validation
 

You're right it works
Thanks

"Frank Kabel" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany


CTInt04 wrote:
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

LEAP YEAR Validation
 

Thanks

"merjet" wrote:

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