DATE Entry

  • Thread starter Thread starter Jim
  • Start date Start date
J

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
 
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
 
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
Click to expand...

Check out Data/Validation


--ron
 
Validation is not adequate really


Jim
Click to expand...

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
 
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
Click to expand...


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
 
Back
Top