how to check if a column contains valid date format

[SamSurya]

I hve a column having date as 30-Jun-20. this column has different date formats. I need to check if the date given is in the format listed below:

The valid values are the following: 'DDMMYYYY' , 'MMDDYYYY' , 'YYYYMMDD' , 'DD-MM-YYYY' , 'MM-DD-YYYY' , 'YYYY-MM-DD' , 'DD.MM.YYYY' , 'MM.DD.YYYY' , 'YYYY.MM.DD' , 'DD/MM/YYYY' , 'MM/DD/YYYY' , 'YYYY/MM/DD' , 'YYYY-MM-DDThh:mm:ss.000000000' , if not  than flag as "Not Valid" else "Valid"

Please help. 

[DavidP]

You would do it with a formula like this. You'll have to test for every type of format in your list.

I'll add some more if I get a chance.

[DavidP · Accepted solution]

I've updated it now with all the options

[JosephSerpis · Accepted solution]

Hi @SamSurya you can do this with Regex match and also check that the date dosent exceed 12 months or the date go past 31 days. Let me know what you think?

[SamSurya]

Amazing..... I was not sure if DateTime Parse would work here ..... thanks ... for showing me new way. this works ... 🙂

[SamSurya]

Wow... Thanks a tonne... This works for me and yes, you gave me a thought, I do get data where dates are in valid format but not correct this check would indeed be really helpful. so is this possible with in Regex or do we need to have additional formula.

[brendafos]

Joseph -

Love your solution.

Thanks for another great example of the power of Regular Expressions!

[SamSurya]

Hi Joseph,

just one more help, could you please help me understanding ""^(19|20)\d\d[- /.](0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])$" .

Thanks 

Sam

[SamSurya]

Yeah.. indeed... I second that

[JosephSerpis]

Hi @SamSurya this link will help explain the Regex.