Solved

Excel Date Auto convert

Posted on 2014-03-04
5
293 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 26

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 26

Author Closing Comment

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

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Viewers will learn the different options available in the Backstage view in Excel 2013.
Video by: Zack
Viewers will learn about various customizable options in Excel 2013.

751 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