Validate data input in DD/MM/YYYY hh:mm format

[Bert_Withpeterson]

Hi all,

I'm looking to use data validation to throw an error if inputted format is not DD/MM/YYYY hh:mm.

This is a standard excel format but I can't wrap my head around how to validate, can anyone help please? Assume the input is in cell A2.

Thank you.

 

[Asbestos_Jen]

You could try custom =AND(A2>=367,A2<=73367) (between 01-Jan-1901 00:00 and 13-Nov-2100 00:00) and format the cell to dd/mm/yyyy hh:mm.

 

[Rick Rothstein]

Just noting for your consideration that if the day number and month number are both less than 13, there would be no way to verify whether the value was correctly entered in DD/MM/YYYY or not. For example, if the date was May 10, 2024 and the value was entered as 05/10/2024, there is no way for you to be able to validate that the date was incorrectly entered.

 

[Asbestos_Jen]

Just noting for your consideration that if the day number and month number are both less than 13, there would be no way to verify whether the value was correctly entered in DD/MM/YYYY or not. For example, if the date was May 10, 2024 and the value was entered as 05/10/2024, there is no way for you to be able to validate that the date was incorrectly entered.

I strongly prefer month names rather than numbers for this reason.