DATE Entry

[Jim]

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

 

[Bernard V Liengme]

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

 

[Ron Rosenfeld]

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

 

[Jim Kenny]

have tried data validation

jim

 

[jim]

Validation is not adequate really


Jim

have tried data validation

jim

 

[Ron Rosenfeld]

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

 

[Jim]

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]

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

 

[jim]

Thanks Ron


That seems to work


Jim

 

[Ron Rosenfeld]

Thanks Ron


That seems to work


Jim

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


--ron