Excel Date Auto convert

Professor J
Professor J used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
Professor JMicrosoft Excel Expert
Top Expert 2014

Author

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.
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
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.
Professor JMicrosoft Excel Expert
Top Expert 2014

Author

Commented:
Thanks Luconsta    this solution helped.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start Today