Solved

Excel Date Auto convert

Posted on 2014-03-04
5
290 Views
Last Modified: 2014-03-04
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.
0
Comment
Question by:ProfessorJimJam
  • 3
  • 2
5 Comments
 
LVL 14

Expert Comment

by:luconsta
ID: 39903275
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
 
LVL 25

Author Comment

by:ProfessorJimJam
ID: 39903460
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
 
LVL 14

Expert Comment

by:luconsta
ID: 39903558
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
 
LVL 14

Accepted Solution

by:
luconsta earned 500 total points
ID: 39903598
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
 
LVL 25

Author Closing Comment

by:ProfessorJimJam
ID: 39903623
Thanks Luconsta    this solution helped.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Note: This is the third blog post in a series on email clearinghouses (https://www.xmatters.com/alert-management/blog-email-has-failed-us?utm_campaign=70138000000ydLoAAI&utm_source=exex&utm_medium=article&utm_content=blog-post).   We’ve been talki…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Viewers will learn how to customize the ribbon and quick access toolbar in Excel 2013.
Viewers will learn the basics of using filtering and sorting in Excel 2013.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now