Excel Date Auto convert

I have cells that requires date input either by typing data in the cell or pasting from another cell.   the inputs have to be only dates  in MM/DD/YYYY format. however some users by mistake might enter DD/MM/YYYY   and therefore i want something like a auto correct or validation that if a user enters data in DD/MM/YYYY then in the cell in converts it and shows it as MM/DD/YYYY.

i tried data validation with text function, somehow i cannot get it right.  

appreciate any help on this.
LVL 27
ProfessorJimJamMicrosoft Excel ExpertAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Lucian ConstantinTrainerCommented:
I don't think you could find a "reliable" way to validate your date because the only thing you could do (even with Data Validation option for dates) is to check if the value is a valid date... and as long as user will enter valued in this interval [1-12]/[1-12]/YYYY you will always have a valid date no matter if your dates are MM/DD/YYYY or DD/MM/YYYY.

One suggestion: you could get closer if the entered or "pasted" text date is somehow related to the current date so you could verify if the date is a valid date +- few days from current date.
0
ProfessorJimJamMicrosoft Excel ExpertAuthor Commented:
Luconsta

thanks for your suggestion.  Yes, the followings dates would be one or few days later the current date.  any idea?

with the current setting i have, is that  if a user enters 01/03/2014  with intention of date 1st March 2014,  my excel file reads this as January 3rd 2014 which is wrong.  i wanted a workaround that Excel would understand, if the user enters 01/03/2014 then it would not accept it becuase most recent date which was entered was March and Not January.

appreciate your feedback.
0
Lucian ConstantinTrainerCommented:
In this case you could use Data Validation as follows:

1. Choose 2 cells in you Excel sheet for minimum/maximum values of the accepted date - let's say you'll accept 15 days before/after current date
2. For the "min" value enter this formula: =TODAY()-15
3. For the "max" value enter: =TODAY+15
4. Set the Data Validation to:
Allow: Date
Data: Between
Start date: "cell with min value"
End date: "cell with max value"


Now test it.

Having the cells as above you could put some warnings from the beginning  - "Please note that the values accepted for dates are between: min/max".

HTH
0
Lucian ConstantinTrainerCommented:
I forgot to mention if you don't want to use "helper cells" you could validate a SINGLE cell as follows (assuming you validate on colum A starting from A2) - using Data Validation select custom and enter the following formula:

=AND(A2>TODAY()-15;A2<TODAY()+15)

Then copying the validated cell down you will see that the validation formula will change accordingly for A3, A4... and so on.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ProfessorJimJamMicrosoft Excel ExpertAuthor Commented:
Thanks Luconsta    this solution helped.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Spreadsheets

From novice to tech pro — start learning today.