Date Data Validation and Copy

[David785]

Perhaps an easily resolved issue, but one that stumps me. I'm building a spreadsheet that allows users to enter a date in a cell (A1). I want to restrict entries to only permit the date as MM/DD/YYYY, to include leading zeros, if necessary, so that the entry is ten digits long.

Then, I want that user entered date to transfer to another cell (B1) and transformed to the date with leading and trailing asterisks (*MM/DD/YYYY*). That entry will be displayed in bar code font. Cell B1 is to be generated by the spreadsheet, so that the user only makes the date entry in cell A1.

Formatting A1 as a date field presented many problems, to include an inability to select the ten-digit format. So I've formatted this cell as a custom cell, which works well in tranforming user entries such as 1/1/09 to the required 01/01/2009.

Problem 1: I can't get the data validation error alert feature to work properly. I set the condition to allow text length equal to 10, and every entry I try produces the error message. I've also set the condition to allow custom data using the formula MM/DD/YYYY. That also produces the error message regardless of entry.

Problem 2: I can't figure out the formula to add the asterisks to the front and back of the date for cell B1. The closest I can get is the asterisks with the date converted to a number.

Any suggestions for this rookie?

[JBeaucaire]

1) I don't see the problem. If you're using a Custom Number format of mm/dd/yyyy then it puts it in the right format, doesn't it? Validation errors are needed why?

2) ="*"&TEXT(A1,"MM/DD/YYYY")&"*"

[David785]

1) The cell is formatted as "custom," with type "MM/DD/YYYY." When I enter a date, the data validation will ensure the value is a ten digit date, with leading zeros automatically entered if needed. That works well. However, if I activate the error alert and set it allow text length to equal ten digits, the error message appears regardless of the data I enter into the cell, even if I enter a valid ten digit date. I need the error alert because I want to prevent the user from entering something other than a date. As it is, the user can pretty much enter anything in the cell.

2) Your formula worked. I had been close to that, but neglected the parentheses for the text format. Thank you, I wasn't aware that those were part of the equation.

[JBeaucaire]

1) ...I need the error alert because I want to prevent the user from entering something other than a date. As it is, the user can pretty much enter anything in the cell.

Ok. I tried Validation as:

Allow: Date
Data: Greater than or equal to
Start Date: 1/1/2009

...and that only got an error if I didn't enter something recognizable as a date.

Give it a try.

[David785]

That WORKED!

I think it was the combination of formatting the cell as a custom, then setting the data validation to demand a date.

You've helped me design exactly what I need. Thank you!