Restricting format of date to text

**sourabh1201** · 06-26-2013, 02:16 AM

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.

**:) Sixthsense :)** · 06-26-2013, 02:20 AM

Precede your entry with single quote or Select the range and format it as text...

**sourabh1201** · 06-26-2013, 02:28 AM

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!

**:) Sixthsense :)** · 06-26-2013, 02:37 AM

It is not their fault to think about restring them... It is the assumption that the excel performs and changing the entries to date's.

Mark those column/range formatting as Text to get rid of this issue...

**sourabh1201** · 06-26-2013, 02:55 AM

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

**:) Sixthsense :)** · 06-26-2013, 02:59 AM

Originally Posted by sourabh1201

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

I am little bit unclear about the above quoted text

can you please brief it?

**:) Sixthsense :)** · 06-26-2013, 04:00 AM

Do right click on sheet tab and select view code and paste the below code. Return to excel and check...

Private Sub Worksheet_Change(ByVal Target As Range)

With Target
    If .Column > 2 Then Exit Sub
    If .Columns.Count > 1 Then Exit Sub
    If Len(.Value) = 0 Then Exit Sub
    
    If .Column = 1 Then
        If UCase(Trim(.Value)) = "ANNUAL" Then .Offset(, 1).NumberFormat = "@"
    Else
        If Not UCase(.Value) Like "##-[A-Z][A-Z][A-Z]" Then
            MsgBox "Your entry should be like ##-MMM Only", vbCritical, "Invalid Entry"
            Application.EnableEvents = False
                .ClearContents
            Application.EnableEvents = True
            .Select
        End If
    End If

End With

End Sub

**st_judeu@yahoo.com** · 06-26-2013, 02:24 AM

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

Then select the date format you want.

**sourabh1201** · 06-26-2013, 02:31 AM

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

**musavir_pasha** · 07-01-2013, 04:54 AM

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.jpg 2013-07-01_165135.jpg.

Please assist.

Thank You,
Musavir Pasha.

**:) Sixthsense :)** · 07-01-2013, 05:07 AM

Hi Musavir Pasha,

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

**musavir_pasha** · 07-01-2013, 12:43 PM

He Buddy,

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

Thank You,
Musavir Pasha.

Restricting format of date to text

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Restricting format of date to text

Re: Restricting format of date to text

Re: Restricting format of date to text

Re: Restricting format of date to text

Re: Restricting format of date to text

Re: Restricting format of date to text

Re: Restricting format of date to text

Re: Restricting format of date to text

Re: Restricting format of date to text

Re: Restricting format of date to text

Re: Restricting format of date to text

Re: Restricting format of date to text

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions