Date format problem while loading data from Excel

I set up validation editor to check to make sure the field FY Date only contained date information:

For values 11/1/2007; 10/1/2008; 11/1/2008 & 12/1/2008, the validation editor validated the data But for the entries were the month is less than 10, it gave warning messages..

I can not figure out why if the date format is set to MM/DD/YYYY it fails for 2/1/2007 but will pass for 11/1/2007.

I even tried going into excel and resetting the format for field "FY Date" to mm/dd/yyyy

Mike

Michael

>>> I can not figure out why if the date format is set to MM/DD/YYYY it fails for 2/1/2007 but will pass for 11/1/2007.

MM/DD/YYYY

MM format requires 2 digits for month EG it should be always 01..12
same for DD

I suggest to try changing format from MM/DD/YYYY to M/D/YYYY

Hope that helps,
Peter

The drop down list does not offer the option to select M/D/YYYY so I just went ahead and typed in M/D/YYYY on the Data Format line and then ran the data through the validation editor and it worked fine.

But MM/DD/YYYY should have work on a date like 1/1/2007.

Anyway, thanks for the tip, I'll keep trying to see if i can get ETL to ETL my data set into the proper, final structure.

Michael