ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Data Validation Date Format (https://www.excelbanter.com/excel-worksheet-functions/220406-data-validation-date-format.html)

Dana M

Data Validation Date Format
 
,I would like to use Data Validation so that users must enter a date in
MM/DD/YY format and the date entered must be after 1/1/2008. I've tried this:

Settings - Allow: Date, Data: greater than or equal to, Start Date: 1/1/2008

I have an input message as well as a STOP Alert. However, it only works
when some types of incorrect data are entered. If the user enters 01012009,
it enters 10/13/70. Any ideas?

N Harkawat

Data Validation Date Format
 
By entering 01012009 excel interprets it as 1012009th day after 01/01/1901
which is Oct-13-4670

As an alternative in data validation you could give between say start date:
01/01/2008" and End date: say 01/01/2011 or some such date



"Dana M" wrote:

,I would like to use Data Validation so that users must enter a date in
MM/DD/YY format and the date entered must be after 1/1/2008. I've tried this:

Settings - Allow: Date, Data: greater than or equal to, Start Date: 1/1/2008

I have an input message as well as a STOP Alert. However, it only works
when some types of incorrect data are entered. If the user enters 01012009,
it enters 10/13/70. Any ideas?


Dana M

Data Validation Date Format
 
Thank you very much, N. That worked nicely!

"N Harkawat" wrote:

By entering 01012009 excel interprets it as 1012009th day after 01/01/1901
which is Oct-13-4670

As an alternative in data validation you could give between say start date:
01/01/2008" and End date: say 01/01/2011 or some such date



"Dana M" wrote:

,I would like to use Data Validation so that users must enter a date in
MM/DD/YY format and the date entered must be after 1/1/2008. I've tried this:

Settings - Allow: Date, Data: greater than or equal to, Start Date: 1/1/2008

I have an input message as well as a STOP Alert. However, it only works
when some types of incorrect data are entered. If the user enters 01012009,
it enters 10/13/70. Any ideas?



All times are GMT +1. The time now is 07:59 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com