Validating Date Format
 

Hi,
We receive a sheet with about 1000 rows and need to do some
validation
on that sheet before it gets processed. One of the validations is for
the column with the date.
I need to validate that the date is in the "mm/dd/yyyy" format. I
have
looked and tried different but have not been able to do this. Could
somebody tell me how to validate the date.

Thank You

Validating Date Format
 

Hi,

Why not just format the whole column to the desired format, then any dates
legal dates must be formatted as you want?

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"exceed_novice" wrote:

Hi,
We receive a sheet with about 1000 rows and need to do some
validation
on that sheet before it gets processed. One of the validations is for
the column with the date.
I need to validate that the date is in the "mm/dd/yyyy" format. I
have
looked and tried different but have not been able to do this. Could
somebody tell me how to validate the date.

Thank You

Validating Date Format
 

Hi

Format the column FormatcellsNumberCustommm/dd/yyyy
Select the columnDataText to columnsNextNextselect Dateselect
M/D/YFinish

--
Regards
Roger Govier

"exceed_novice" wrote in message
...
Hi,
We receive a sheet with about 1000 rows and need to do some
validation
on that sheet before it gets processed. One of the validations is for
the column with the date.
I need to validate that the date is in the "mm/dd/yyyy" format. I
have
looked and tried different but have not been able to do this. Could
somebody tell me how to validate the date.

Thank You

Validating Date Format
 

It depends on what data you have. Is there a reason you couldn't provide
examples, and tell what what you tried and why it doesn't work? For example,
did you try Excel's Datevalue function?

And remember, no amount of validation can determine that 12/01/2009 should
really be Jan 12/09.

Regards,
Fred


"exceed_novice" wrote in message
...
Hi,
We receive a sheet with about 1000 rows and need to do some
validation
on that sheet before it gets processed. One of the validations is for
the column with the date.
I need to validate that the date is in the "mm/dd/yyyy" format. I
have
looked and tried different but have not been able to do this. Could
somebody tell me how to validate the date.

Thank You

Validating Date Format
 

This will work even if your dates are text strings, which formatting as a date will not correct.
Copy the code into a regular codemodule, and use it like

=CheckDate(A2,"mm/dd/yyyy")

Copy down to match your cells.

HTH,
Bernie
MS Excel MVP


Function CheckDate(myC As Range, myF As String) As Variant
CheckDate = (myC.Text = Format(myC.Value, myF))
End Function


"exceed_novice" wrote in message
...
Hi,
We receive a sheet with about 1000 rows and need to do some
validation
on that sheet before it gets processed. One of the validations is for
the column with the date.
I need to validate that the date is in the "mm/dd/yyyy" format. I
have
looked and tried different but have not been able to do this. Could
somebody tell me how to validate the date.

Thank You

Validating Date Format
 

Thanks to allof you fo rthe responses. I will try them out.

On Feb 6, 9:04*pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
This will work even if your dates are text strings, which formatting as a date will not correct.
Copy the code into a regular codemodule, and use it like

=CheckDate(A2,"mm/dd/yyyy")

Copy down to match your cells.

HTH,
Bernie
MS Excel MVP

Function CheckDate(myC As Range, myF As String) As Variant
CheckDate = (myC.Text = Format(myC.Value, myF))
End Function

"exceed_novice" wrote in message

...

Hi,
We receive a sheet with about 1000 rows and need to do some
validation
on that sheet before it gets processed. One of the validations is for
the column with the date.
I need to validate that the date is in the "mm/dd/yyyy" format. I
have
looked and tried different but have not been able to do this. Could
somebody tell me how to validate the date.

Thank You