Several persons are using my spreadsheet and they enter the date in
different formats (12/11/2003 or 1.2.03). This date is used in a calculation
and requires the format DD/MM/YYYY. Is there a way that I can reject a date
that is not in the required format.

Thank you

Jim

From an answer I gave in another groupd 2 days ago

Normally all that is needed to change from US <-> Rest of World dates scheme
is to use Format|Cells|Dates, But I suspect the dots in you dates are
causing a problem. You may need to extract the three numeric values and make
a 'date' out of them.

Here is one way with your date in A2 (there could well be better ways -
text functions are not my speciality)
dd(in C2) =MID(A2,1,FIND(".",A2)-1)
mm(in D2)
=MID(A2,FIND(".",A2)+1,FIND(".",A2,FIND(".",A2)+1)-FIND(".",A2)-1)
yy(in E2) =RIGHT(A2,2)
date =DATE(E2,D2,C2)

Having got the correct dates you can use Copy followed by Paste Special as
Values to let you delete all the intermediate stuff.

Bernard


"Jim" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Several persons are using my spreadsheet and they enter the date in
> different formats (12/11/2003 or 1.2.03). This date is used in a
calculation
> and requires the format DD/MM/YYYY. Is there a way that I can reject a
date
> that is not in the required format.
>
> Thank you
>
> Jim
>
>

On Wed, 22 Oct 2003 16:08:35 +0100, "Jim" <(E-Mail Removed)> wrote:

>Several persons are using my spreadsheet and they enter the date in
>different formats (12/11/2003 or 1.2.03). This date is used in a calculation
>and requires the format DD/MM/YYYY. Is there a way that I can reject a date
>that is not in the required format.
>
>Thank you
>
>Jim
>

Check out Data/Validation


--ron

have tried data validation

jim
"Jim" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Several persons are using my spreadsheet and they enter the date in
> different formats (12/11/2003 or 1.2.03). This date is used in a
calculation
> and requires the format DD/MM/YYYY. Is there a way that I can reject a
date
> that is not in the required format.
>
> Thank you
>
> Jim
>
>

Validation is not adequate really


Jim


"Jim Kenny" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> have tried data validation
>
> jim
> "Jim" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Several persons are using my spreadsheet and they enter the date in
> > different formats (12/11/2003 or 1.2.03). This date is used in a
> calculation
> > and requires the format DD/MM/YYYY. Is there a way that I can reject a
> date
> > that is not in the required format.
> >
> > Thank you
> >
> > Jim
> >
> >
>
>

On Thu, 23 Oct 2003 09:15:06 +0100, "jim" <(E-Mail Removed)> wrote:

>Validation is not adequate really
>
>
>Jim

Well it would reject dates that are not in the required format, which is what
you asked for.

Do you have some additional requirements that you did not state?


--ron

Needs to regect dates that are not in the correct format such as 1.1.03
which is being accepted as a time.Validation seems to check for certain
dates


Jim



"Ron Rosenfeld" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Thu, 23 Oct 2003 09:15:06 +0100, "jim" <(E-Mail Removed)> wrote:
>
> >Validation is not adequate really
> >
> >
> >Jim
>
> Well it would reject dates that are not in the required format, which is
what
> you asked for.
>
> Do you have some additional requirements that you did not state?
>
>
> --ron

On Thu, 23 Oct 2003 17:20:46 +0100, "Jim" <(E-Mail Removed)> wrote:

>Needs to regect dates that are not in the correct format such as 1.1.03
>which is being accepted as a time.Validation seems to check for certain
>dates
>
>
>Jim
>


Well if I use Data Validation, set it to dates, and enter a date range such as
1/1/1920 to 1/1/2999, 1.1.03 gets rejected; as do entries that could be
accepted as times here in the US. Such as 1:10:00


What regional settings are you using?


--ron

Thanks Ron


That seems to work


Jim


"Ron Rosenfeld" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Thu, 23 Oct 2003 17:20:46 +0100, "Jim" <(E-Mail Removed)>
wrote:
>
> >Needs to regect dates that are not in the correct format such as 1.1.03
> >which is being accepted as a time.Validation seems to check for certain
> >dates
> >
> >
> >Jim
> >
>
>
> Well if I use Data Validation, set it to dates, and enter a date range
such as
> 1/1/1920 to 1/1/2999, 1.1.03 gets rejected; as do entries that could be
> accepted as times here in the US. Such as 1:10:00
>
>
> What regional settings are you using?
>
>
> --ron

On Fri, 24 Oct 2003 13:25:38 +0100, "jim" <(E-Mail Removed)> wrote:

>Thanks Ron
>
>
>That seems to work
>
>
>Jim

You're welcome. Glad it's working for you.


--ron