Automatic Date Formating of Data Validation Function.

Posted by Bill on September 12, 2001 8:13 AM

Hi.

I have a little problem with a formatting code...

I need to have a date that is entered in a cell to display as MMDDYYYY, with no slashes or dashes or anything. I am trying to set up a cell with data validation to only allow a date to be entered. It works as far as converting the data entered in the cell to a date format, but although I can get it to DISPLAY as MMDDYYYY, the actual data in the cell is converted to M/D/YYYY. How do I alter the actual conversion of the data actually in the cell? (P.S - using a formula is not an option. This is for a simplified upload situation).

Please help.

Bill.

Posted by Russell Hauf on September 12, 2001 9:32 AM

This is going to involve a formula but hear me out. You could add a column, then put the formula:

=TEXT(A1, "MMDDYYYY")

Then you could copy and paste special - values. You could then get rid of your original column with the dates in it.

This could also be automated with VBA.

I hope this at least sparks an idea,

Russell

Posted by Bill on September 14, 2001 1:19 PM

Thanks for your help. I was wondering what the worksheet function to convert text to a date format was anyway. I am not sure about the macro to remove columns etc, but you've started my mind going in a good direction.

Regards,

Bill.