Validating specific date format in Excel

Question

I need to verify whether the dates in my Excel table are in the correct format, which is yyyy/mm/dd, so any other formats like mm/dd/yyyy would be incorrect.

I am using this formula:

=IF(NOT(ISERROR(DATEVALUE(TEXT(W2,"yyyy/mm/dd")))),"valid date","invalid date"

While the formula did pick up cells that did not contain a date, it failed to pick up cells that had dates in the wrong format like mm/dd/yyyy. What am I missing from my formula? Please help.

Thank you in advance.

Kev

A true date is a number, specifically the number of days since 1/1/1900. You can format this number any way you want. There is no way via formula that will allow you to test the format of a cell. The best method is to format the cells prior to entering the date and Excel will then take any valid date and display it in the format you choose. — Scott Craner

Gary's Student Gary's Student · Accepted Answer · 2018-02-23T21:20:11

If you want to check the format of a cell, put this User Defined Function in a standard module:

Public Function FormatChecker(r As Range) As String
    FormatChecker = r(1).NumberFormat
End Function

With data in column A, in B1 enter:

=FormatChecker(A1)

and copy down. In C1 enter:

=IF(AND(LEFT(B1,4)="yyyy",MID(B1,6,2)="mm",RIGHT(B1,2)="dd"),"Good","Bad")

NOTE:

If you change the format of a column A cell, you will also need to refresh its contents to re-calculate column B. Use: F2 and Enter

Validating specific date format in Excel

1 Answers