Excel Date Entry Question 2

[Leslie67]

I've got a form that's going to be used for data entry.

I have cell formatting on so that dates entered come out as "November 3, 2003".

This works fine if someone enters "11/03/03", however when you enter "110303" you get the date relative to January 1, 1900.

Does anyone have a solution? My problem is that a lot of different people will be using this form, so I'm trying to anticipate all kinds of data entry for the date.

 

[xlbo]

I am assuming that you have built a "form" from a worksheet rather than using a userform....

If this is the case, have a look at Data>Validation and chooose "Date"
You can restrict date entry ranges ie >date and < date2

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[Leslie67]

Yes, it's from a worksheet. I didn't build it, but I'm helping out.

My boss wanted a different answer, but that's kinda what I figured the solution was going to be for both the date and the time references that I have.

Thanks for your quick answer, Geoff!

 

[SkipVought]

Leslie,

ALL dates are stored with a number relative to 1/1/1900. Excel is smart enough to figure out that when you enter 11/3/2003, you are referring to a date and it stores that date as 37928 and formats it is the manner you have dictated.

110303 is the stored value for the date, 12/30/2201.

So it all works out.

What you need is an &quot;input mask&quot; that takes mmddyy and converts it to mm/dd/yy so that excel will use that format to interpret the data rather than the raw numeric value.



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Leslie67]

Hey Skip...

Any hints how to do the &quot;input mask&quot; thing? <grin>

 

[SkipVought]

No, don't know how to do the mask thing like MS Access.

However, here's a way to do it with the Worksheet_Change event.

1. Name the date range, &quot;DateRange&quot;

2. In the Worksheet object, paste this code...

Dim bChange As Boolean
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    If bChange Then Exit Sub
    bChange = True
    Set rng = Intersect(Target, Range("DateRange"))
    If Not rng Is Nothing Then
        With Target
           .Value = Left(.Value, 2) & "/" & Mid(.Value, 3, 2) & "/" & Right(.Value, 2)
        End With
    End If
    bChange = False
End Sub

Hope this helps


Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Leslie67]

Thanks, Skip, but I'm getting a &quot;data mismatch error&quot; in the &quot;.value =&quot; line.

Any idea why? Can you not use .value with a &quot;Range&quot; data type?

 

[SkipVought]

Set up a debug Watch Window for Target.Value and tell me what you see.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Bowers74]

This might help you out:

If you set up the Data Validation for the Date cell as Custom Data Validation, you can stop users from entering dates w/o the slashes (or periods, dashes, etc...).

Select the cell where you want to set up Validation and go to Data->Validation.... Select &quot;Custom&quot; from the drop-down list and enter the following formula into the formula field at the bottom:

=AND(MID($A$1,3,1)<>&quot;/&quot;,MID($A$1,6,1)<>&quot;/&quot

This formual assumes that your Date field is cell A1 (which I'm sure it isn't). You will have to change the absolute references to match your Date field.

If you want to force periods or dashes instead of slashes, then change the slashes to periods or dashes.

Unfortunately, your users will have to enter every date in the format mm/dd/yyyy (i.e. January 1st 2003 as 01/01/2003 and October 14th 2003 as 10/14/2003) or else they will receive an error message.

NOTE: Data Validation in Excel, also lets you create an Input Message that pops up every time the cell is entered, and a customized Error Message for when (in this case) the date is entered in the wrong format (eg. &quot;You have entered the date incorrectly, please re-enter the date in the correct format dd/mm/yyyy.&quot;

I hope this helps!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[Leslie67]

Hey Skip:

This is what I get:

Target.value - type variant/variant(1 to 1, 1 to 3)
target.value(1,1) - value #11/03/2003# type variant/date
target.value(1,2) - value Empty type variant/empty
target.value(1,3) - value Empty type variant/empty

 

[Leslie67]

Mike:

Thank you so much. I'm going to file this away for future reference. I would *love* to do it this way, if only for consistency's sake.

But....

TBTB want it this other way. Nothing like giving me a challenge <g>

 

[SkipVought]

In the worksheet_Change event code,
Target is a range object
Target.Value is the Value of Target, assuming that Target is a single cell range.

I don't know what you are referring to.

My code stated...

        With Target
           .Value = Left(.Value, 2) & "/" & Mid(.Value, 3, 2) & "/" & Right(.Value, 2)
        End With

which parses Target.Value

"LLMMRR"

into the left 1/3, middle 1/3 and right 1/3, placing a &quot;/&quot; between each third and returning that constructed string to Target.Value in the form...

"LL/MM/RR"

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Bowers74]

Hi Leslie,

I'm glad you found it helpful . . .




Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[Leslie67]

Skip, I copied and pasted your code exactly as was.

I'm not sure where the error is coming from. You asked what I got when I put a &quot;watch&quot; on Target.Value, and that's what I've got when it stops with the &quot;type mismatch&quot; error.

Stepping through the code, Target.value never changes it's &quot;value&quot; because it never gets evaluated. The &quot;.value =&quot; line is the one where the mismatch occurs.

Oh well... it was worth a shot. Thanks, Skip.

 

[SkipVought]

Les,

Seems to me that you may have pasted something into DateRange -- is that what happened?

I just retested my code, and if I enter

110303

in any cell in DateRange, I get the returned value

11/03/03

In that cell.

Tell me what's heppnin'!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

I had a bug in my procedure. After the first date is entered in DateRange, Excel assumes that that number is a date. So, have to set the NumberFormat for Target to &quot;General&quot;...

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    If bChange Then Exit Sub
    bChange = True
    Set rng = Intersect(Target, Range("DateRange"))
    If Not rng Is Nothing Then
        With Target
            .NumberFormat = "General"
           .Value = Left(.Value, 2) & "/" & Mid(.Value, 3, 2) & "/" & Right(.Value, 2)
        End With
    End If
    bChange = False
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

Oh my! I found another glitch with months less than 10.

Here is the New 'n' improved...

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    If bChange Then Exit Sub
    bChange = True
    Set rng = Intersect(Target, Range("DateRange"))
    If Not rng Is Nothing Then
        With Target
            .NumberFormat = "General"
            Select Case Len(.Value)
            Case 5
                iLen = 1
            Case 6
                iLen = 2
            End Select
           .Value = Left(.Value, iLen) & "/" & Mid(.Value, Len(.Value) - 3, 2) & "/" & Right(.Value, 2)
            .NumberFormat = "mmmm d, yyyy"
        End With
    End If
    bChange = False
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Leslie67]

Skip:

I'm really appreciating all this... and I've copied and pasted your code as is.

I decided that it can't be your code <g>, so it has to be something that I'm doing to cause these errors.

I realized that I hadn't protected the sheet, so I did that, and now it's telling me:

&quot;Unable to set the NumberFormat property of the Range class&quot;

<sigh>

Would it be unprofessional to chuck my monitor out the window?

 

[Bowers74]

This would've be a lot easier my way!

Sucks that you have to do it this way! I hope that you can use it in the future for something else.

Thanks for the Star too, by the way!






Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[SkipVought]

Les,

Y'know, Bowers is correct. Users should NOT be encouraged to enter dates in Excel in a format that is NOT native date friendly. It encourages sloppy data entry habits.

This exersize was more or less a challenge for me, but it is NOT one that I would adopt for my users.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com