I'm sure this isn't the first time this problem has cropped up so I'm asking
for assistance.

I have a worksheet that requires the user to enter dates into several cells
in the workbook. The dates are displayed as MMM-YY. If the user enters
9/01, they get 9/1/2006 instead of 9/2001. Is there any way to ensure that
they enter the dates correctly. I'd like to add it to the worksheet change
event if necessary.

Thanks

If you want the format of "9/2001", I would think you could just format the
cell as:

m/yyyy


"Barb Reinhardt" <(E-Mail Removed)> wrote in message
news:879F9EDE-36C4-4A65-A31B-(E-Mail Removed)...
> I'm sure this isn't the first time this problem has cropped up so I'm
> asking
> for assistance.
>
> I have a worksheet that requires the user to enter dates into several
> cells
> in the workbook. The dates are displayed as MMM-YY. If the user enters
> 9/01, they get 9/1/2006 instead of 9/2001. Is there any way to ensure
> that
> they enter the dates correctly. I'd like to add it to the worksheet
> change
> event if necessary.
>
> Thanks

That doesn't solve the problem if the user enters the date as 9/01. It
assumes that what's entered is the month and DAY, not the year. I need to
ensure that they enter the year and am not sure how to address this, hence my
post.

"PCLIVE" wrote:

> If you want the format of "9/2001", I would think you could just format the
> cell as:
>
> m/yyyy
>
>
> "Barb Reinhardt" <(E-Mail Removed)> wrote in message
> news:879F9EDE-36C4-4A65-A31B-(E-Mail Removed)...
> > I'm sure this isn't the first time this problem has cropped up so I'm
> > asking
> > for assistance.
> >
> > I have a worksheet that requires the user to enter dates into several
> > cells
> > in the workbook. The dates are displayed as MMM-YY. If the user enters
> > 9/01, they get 9/1/2006 instead of 9/2001. Is there any way to ensure
> > that
> > they enter the dates correctly. I'd like to add it to the worksheet
> > change
> > event if necessary.
> >
> > Thanks
>
>
>

I think the safest way is to enter a real date. One that includes day, month,
year.

But if I entered: 9/2001
excel saw it as September 1, 2001.



Barb Reinhardt wrote:
>
> I'm sure this isn't the first time this problem has cropped up so I'm asking
> for assistance.
>
> I have a worksheet that requires the user to enter dates into several cells
> in the workbook. The dates are displayed as MMM-YY. If the user enters
> 9/01, they get 9/1/2006 instead of 9/2001. Is there any way to ensure that
> they enter the dates correctly. I'd like to add it to the worksheet change
> event if necessary.
>
> Thanks

--

Dave Peterson

Sorry about that. I tested by entering 9/06 and it returned "9/2006". It,
as you know, didn't work when typing "9/01".

What is the format in which the user is suppose to enter the date? Or what
are all the different formats in which someone could or is allowed to enter
the day. Once that is known, then appropriate steps may be taken to ensure
the final result is as you wish.



"Barb Reinhardt" <(E-Mail Removed)> wrote in message
news:31722C50-34F6-480F-AF17-(E-Mail Removed)...
> That doesn't solve the problem if the user enters the date as 9/01. It
> assumes that what's entered is the month and DAY, not the year. I need
> to
> ensure that they enter the year and am not sure how to address this, hence
> my
> post.
>
> "PCLIVE" wrote:
>
>> If you want the format of "9/2001", I would think you could just format
>> the
>> cell as:
>>
>> m/yyyy
>>
>>
>> "Barb Reinhardt" <(E-Mail Removed)> wrote in
>> message
>> news:879F9EDE-36C4-4A65-A31B-(E-Mail Removed)...
>> > I'm sure this isn't the first time this problem has cropped up so I'm
>> > asking
>> > for assistance.
>> >
>> > I have a worksheet that requires the user to enter dates into several
>> > cells
>> > in the workbook. The dates are displayed as MMM-YY. If the user
>> > enters
>> > 9/01, they get 9/1/2006 instead of 9/2001. Is there any way to ensure
>> > that
>> > they enter the dates correctly. I'd like to add it to the worksheet
>> > change
>> > event if necessary.
>> >
>> > Thanks
>>
>>
>>

While I agree with you in theory, in practice, the folks who might use this
form have been given that instruction and aren't doing it. I want to build
in a more fool proof method.

"Dave Peterson" wrote:

> I think the safest way is to enter a real date. One that includes day, month,
> year.
>
> But if I entered: 9/2001
> excel saw it as September 1, 2001.
>
>
>
> Barb Reinhardt wrote:
> >
> > I'm sure this isn't the first time this problem has cropped up so I'm asking
> > for assistance.
> >
> > I have a worksheet that requires the user to enter dates into several cells
> > in the workbook. The dates are displayed as MMM-YY. If the user enters
> > 9/01, they get 9/1/2006 instead of 9/2001. Is there any way to ensure that
> > they enter the dates correctly. I'd like to add it to the worksheet change
> > event if necessary.
> >
> > Thanks
>
> --
>
> Dave Peterson
>

I guess you could format the cell as text and keep the entry as text--not a real
date.

Or you could format the cell as text, then have an event macro that changes the
entry into the date you really want. But this could be a problem if the user
had to change an existing date.

But I think that training the user would be easier.



Barb Reinhardt wrote:
>
> While I agree with you in theory, in practice, the folks who might use this
> form have been given that instruction and aren't doing it. I want to build
> in a more fool proof method.
>
> "Dave Peterson" wrote:
>
> > I think the safest way is to enter a real date. One that includes day, month,
> > year.
> >
> > But if I entered: 9/2001
> > excel saw it as September 1, 2001.
> >
> >
> >
> > Barb Reinhardt wrote:
> > >
> > > I'm sure this isn't the first time this problem has cropped up so I'm asking
> > > for assistance.
> > >
> > > I have a worksheet that requires the user to enter dates into several cells
> > > in the workbook. The dates are displayed as MMM-YY. If the user enters
> > > 9/01, they get 9/1/2006 instead of 9/2001. Is there any way to ensure that
> > > they enter the dates correctly. I'd like to add it to the worksheet change
> > > event if necessary.
> > >
> > > Thanks
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson