Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
convert serial date format to normal date format | Excel Discussion (Misc queries) | |||
validating format | Excel Discussion (Misc queries) | |||
validating telephone format | Excel Worksheet Functions | |||
Convert date + time text format to date format | Excel Worksheet Functions | |||
Validating a date field | Excel Discussion (Misc queries) |