Solved

Excel Date Auto convert

Posted on 2014-03-04
5
291 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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Automate iseries AS/400 excel import to database file 3 40
Excel multiple conditions (text) 4 51
Excel 2016 - Row 1 missing 5 77
Button in Excel 5 88
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
Viewers will learn the different options available in the Backstage view in Excel 2013.
Viewers will learn the basics of formula auditing in Excel 2013.

832 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