dating for successful people - Validating date

To download the sample file, click here: Data Validation for Dates The zipped file is in xlsx format, and does not contain any macros.

validating date-47

Otherwise you can end up with values in the database that have no relation to reality.

In this example, the date fields will only accept input that matches the pattern 'dd/mm/yyyy' (this could just as easily be changed to 'yyyy-mm-dd' or 'mm/dd/yyyy').

In this video, three different methods are used to validate dates.

From the Allow drop down in the data validation settings, the following options will be used: Written instructions, and the sample file, are below the video.

In this example, the sheet with the list is named Admin Lists.

To test the data validation, enter a date that is not in the current year.However, it’s a bit hard to validate the leap year , 30 or 31 days of a month, we may need basic logic as below. “31/11/2010” – November has 30 days only [Parser] Running: E:\workspace\mkyong\Date is valid : 1/1/2010 , true Date is valid : 01/01/2020 , true Date is valid : 31/1/2010 , true Date is valid : 31/01/2020 , true Date is valid : 29/2/2008 , true Date is valid : 29/02/2008 , true Date is valid : 28/2/2009 , true Date is valid : 28/02/2009 , true Date is valid : 31/3/2010 , true Date is valid : 31/03/2010 , true Date is valid : 30/4/2010 , true Date is valid : 30/04/2010 , true Date is valid : 31/5/2010 , true Date is valid : 31/05/2010 , true Date is valid : 30/6/2010 , true Date is valid : 30/06/2010 , true Date is valid : 31/7/2010 , true Date is valid : 31/07/2010 , true Date is valid : 31/8/2010 , true Date is valid : 31/08/2010 , true Date is valid : 30/9/2010 , true Date is valid : 30/09/2010 , true Date is valid : 31/10/2010 , true Date is valid : 31/10/2010 , true Date is valid : 30/11/2010 , true Date is valid : 30/11/2010 , true Date is valid : 31/12/2010 , true Date is valid : 31/12/2010 , true Date is valid : 32/1/2010 , false Date is valid : 32/01/2020 , false Date is valid : 1/13/2010 , false Date is valid : 01/01/1820 , false Date is valid : 29/2/2007 , false Date is valid : 29/02/2007 , false Date is valid : 30/2/2008 , false Date is valid : 31/02/2008 , false Date is valid : 29/a/2008 , false Date is valid : a/02/2008 , false Date is valid : 333/2/2008 , false Date is valid : 29/02/200a , false Date is valid : 31/4/2010 , false Date is valid : 31/04/2010 , false Date is valid : 31/6/2010 , false Date is valid : 31/06/2010 , false Date is valid : 31/9/2010 , false Date is valid : 31/09/2010 , false Date is valid : 31/11/2010 , false PASSED: Valid Date Test("1/1/2010") PASSED: Valid Date Test("01/01/2020") PASSED: Valid Date Test("31/1/2010") PASSED: Valid Date Test("31/01/2020") PASSED: Valid Date Test("29/2/2008") PASSED: Valid Date Test("29/02/2008") PASSED: Valid Date Test("28/2/2009") PASSED: Valid Date Test("28/02/2009") PASSED: Valid Date Test("31/3/2010") PASSED: Valid Date Test("31/03/2010") PASSED: Valid Date Test("30/4/2010") PASSED: Valid Date Test("30/04/2010") PASSED: Valid Date Test("31/5/2010") PASSED: Valid Date Test("31/05/2010") PASSED: Valid Date Test("30/6/2010") PASSED: Valid Date Test("30/06/2010") PASSED: Valid Date Test("31/7/2010") PASSED: Valid Date Test("31/07/2010") PASSED: Valid Date Test("31/8/2010") PASSED: Valid Date Test("31/08/2010") PASSED: Valid Date Test("30/9/2010") PASSED: Valid Date Test("30/09/2010") PASSED: Valid Date Test("31/10/2010") PASSED: Valid Date Test("31/10/2010") PASSED: Valid Date Test("30/11/2010") PASSED: Valid Date Test("30/11/2010") PASSED: Valid Date Test("31/12/2010") PASSED: Valid Date Test("31/12/2010") PASSED: In Valid Date Test("32/1/2010") PASSED: In Valid Date Test("32/01/2020") PASSED: In Valid Date Test("1/13/2010") PASSED: In Valid Date Test("01/01/1820") PASSED: In Valid Date Test("29/2/2007") PASSED: In Valid Date Test("29/02/2007") PASSED: In Valid Date Test("30/2/2008") PASSED: In Valid Date Test("31/02/2008") PASSED: In Valid Date Test("29/a/2008") PASSED: In Valid Date Test("a/02/2008") PASSED: In Valid Date Test("333/2/2008") PASSED: In Valid Date Test("29/02/200a") PASSED: In Valid Date Test("31/4/2010") PASSED: In Valid Date Test("31/04/2010") PASSED: In Valid Date Test("31/6/2010") PASSED: In Valid Date Test("31/06/2010") PASSED: In Valid Date Test("31/9/2010") PASSED: In Valid Date Test("31/09/2010") PASSED: In Valid Date Test("31/11/2010") =============================================== com.mkyong.regex.The function has been improved now so that the day, month and year values are checked to ensure that they're in the right ball-bark (ie. Also the year must be between 1902 and the current year.The year limitation would be used if you were asking for a date of birth or date of some recent event.The function example is comparing a string to a known date format.

Tags: , ,