How can I keep the mm/dd/yy format to display an error or message when other
users enter the wrong date: ie 12/31/20081?

You can use data validation to reject an invalid date such as 31/12/08 if
you want 12/31/08, but not if someone puts in 7/11/08 instead of 11/7/08.
--
David Biddulph

"jvq" wrote in message
...
How can I keep the mm/dd/yy format to display an error or message when
other
users enter the wrong date: ie 12/31/20081?

You can even use conditional formatting with the below formula:

=ISERR(MONTH($F$9))

and put some color like red. So any cell where the data is not in date
format will, change to red color.

but this formatting and the solution by david will not work if somebody
enters 1/2/2008 (1st feb 2008) instead of 2/1/2008 (2nd jan 2008).

--
Kind Regards,
Satti Charvak
Only an Excel Enthusiast
Noida, India


"jvq" wrote:

How can I keep the mm/dd/yy format to display an error or message when other
users enter the wrong date: ie 12/31/20081?

I think you should allow the users to enter the date in any format that's
valid.

But you can format the cell anyway you want--but I would use an unambiguous
format so that it's easy to verify the dates.

jvq wrote:

How can I keep the mm/dd/yy format to display an error or message when other
users enter the wrong date: ie 12/31/20081?

--

Dave Peterson

i wonder if he meant 20081 not being a valid year?

Dave Peterson wrote:

I think you should allow the users to enter the date in any format that's
valid.

But you can format the cell anyway you want--but I would use an unambiguous
format so that it's easy to verify the dates.

jvq wrote:

How can I keep the mm/dd/yy format to display an error or message when other
users enter the wrong date: ie 12/31/20081?

Could be, but then I bet the non-ambiguous date format would show that as an
error right away. And I bet David's data|validation rule would catch that.

Bob I wrote:

i wonder if he meant 20081 not being a valid year?

Dave Peterson wrote:

I think you should allow the users to enter the date in any format that's
valid.

But you can format the cell anyway you want--but I would use an unambiguous
format so that it's easy to verify the dates.

jvq wrote:

How can I keep the mm/dd/yy format to display an error or message when other
users enter the wrong date: ie 12/31/20081?



--

Dave Peterson

Yes, I meant 12/31/20081 as the wrong date. I use filters to run reports and
wrong dates mess up my reports. Data validation did the trick. Thank you!

"David Biddulph" wrote:

You can use data validation to reject an invalid date such as 31/12/08 if
you want 12/31/08, but not if someone puts in 7/11/08 instead of 11/7/08.
--
David Biddulph

"jvq" wrote in message
...
How can I keep the mm/dd/yy format to display an error or message when
other
users enter the wrong date: ie 12/31/20081?