Validating Date Format

[centralcal]

Hi:

I need to verify whether the dates in my Excel table 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(<wbr style="font-family: Calibri; font-size: 14.6667px;">TEXT(W2,"yyyy/mm/dd")))),"<wbr style="font-family: Calibri; font-size: 14.6667px;">valid date","invalid date")

While the formula did pick up cells that did not contain 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

 

[JoeMo]

Not sure you can do that with a formula. Dates in Excel are serial numbers. Today's date 2/23/2018 is seen as the number 43154, no matter what format you apply. You could use VBA to check using something like this:

Dim ValidFormat as Boolean
ValidFormat = Range("W2").Text Like "####/##/##"

but you would have to add some checking to ensure that dates formatted as "yyyy/dd/mm" are caught.

 

[etaf]

dates are just a number of days from 1/1/1900
The format DD/MM etc is based on that Number

change the format to Number and all the dates that are "real" dates as far as excel is concerned will show as numbers
anything that is left in
"yyyy/mm/dd", OR "mm/dd/yyyy"
Are TEXT formats and not dates

Check and post back if you get just numbers or a mixture
if a mixture then excel will have an issue
also
US date 3rd April - 04/03/YYYY in format DD/MM/YYYY
OR
UK date 03/04/YYYY

Excel will have a number of days since 1900 and so that number will display in whatever format you like
BUT will be a "real date"


in UK Or US will be missread

3rd April
Or
4th March

number

43193.00

43163.00

<colgroup><col></colgroup><tbody>
</tbody>