Restricting format of date to text

Hi guys,

I need to restrict entry of data in a particular cell. It must be in a "dd-mmm" format. But the catch is that it can't be in date format. It has to be only in text format. I need it in text format only, to be able to upload it to a custom software my office uses. The problem is whenever I enter "26-JUN" Excel automatically converts it to a date format.

So to sum it up I need to be able to restrict entry of data in a cell to [number][number]-[char][char][char] without automatically converting it into a date format.

Thank you.

Go to: Format > Format Cells > Number > Date.

Then select the date format you want.

Hi Sixth Sense,

I don't have a problem when I enter the data. The problem is there are about 15 people who enter the data and I have to make corrections in the end. So I want to restrict the type of data they enter. Only in the "dd-mmm" format but in text format.

Thank you for responding!

Hey st_judeo,

The problem is I can't have it in date format. It has to be only in text format but it is entered in "dd-mmm" format.

Thanks

Hi Sixth Sense,

I will do that. There is a small concern though.

The entire range cannot be converted to text.

Only if "Annual" is entered in A1 then B1 has to be automatically formatted to TEXT. If A1 has a other entries from the drop down box then there are other data validations I have applied for B1.

Also I was wondering if there is any way I can restrict entry to only "dd-MMM" when the cell is in text format.

Thank you

With a little bit of tinkering I have figured out how to keep it to TEXT format. But the issue is I get a lot of entries like "26jun" or "26/jun/2013" without any particular format and there are hundreds of entries which I cannot correct before uploading to the software. Eventhough the cell is in text format they should only be able to enter two numbers a hyphen and three alphabets in BOLD letters. Otherwise the software doesn't accept it. It only has to be "26-JUN" in TEXT format only. Any different and it is rejected!

Hi Sixth Sense,

Worked brilliantly. Thanks a lot. Helped me reduce atleast two hours of work everyday. Very grateful for your support.

Hi There,

I have attached a file where one of the field has a value as "22-08" which is the address, however when this file is opened in excel that field is converted in to date2013-07-01_153310.jpg2013-07-01_165135.jpg.

Please assist.

Thank You,
Musavir Pasha.

He Buddy,

Sorry for that, I could not find the way to start a new thread. Please assist.

Thank You,
Musavir Pasha.