Detecting String Format before Converting it Into Date

I have an excel Table with a Date Column that is populated with different dates in string format.
Since its in excel, the type of the column comes under “General”.

The issue I am facing is as follows: the format can be anything in Excel as shown in the list below:

image

So parsing the String that comes from an Excel file becomes difficult, since the format can be anything present in the list above.

Is there any way to Detect the Format a given String is currently in, before we can parse it into Date?

Kind Regards,
Ashwin A.K

Hi @ashwin.ashok

I think that you don’t need to worry about the format, just pass the right culture info and let .NET deal with the conversion.

cultureInfo = CultureInfo.CreateSpecificCulture("en-IN"); // English-India
myDate = DateTime.Parse(anyDateFormat, cultureInfo)

This will work for all formats that you shown.

PS: CultureInfo requires System.Globalization namespace in your Imports.

Hi @alexandretperez ,

Thank you for your response, you see the thing is the Dates are coming from 27 different types of invoices that even our client isn’t sure how many date formats are there.

Here are few formats I encountered:
10/03/2021
21.10.2021
2nd March 2021

This is where it gets tricky, and the solution you quoted works only for the first format. And yes, I have imported the System.Globalization Namespace as you suggested.

image

Any idea how we can handle the rest?

Kind Regards
Ashwin A.K

@ashwin.ashok - the reason why 5/28 date failed because that is not in indian date format. You can play around all these date formats in Immediate panel.

Hi Ashwin ,

Can you please try using Cdate(Any date string).ToString(to requried format)

Hi @Krishna_547 ,

Thank you for your response, but that didn’t seem to work either.

Kind Regards,
Ashwin A.K

Try this

Date.Parse(Regex.Replace(dtString, "(?<=\d)[dhnrst]{2}", ""), New CultureInfo("en-IN")).ToString("dd-MM-yyyy")

dtString is input date string

1 Like

Hi @kumar.varun2 ,

Your solution runs into an error at the third entry:

image

Kind Regards,
Ashwin A.K

It should work. Just check if it is picking the right string or not.

Hi @ashwin.ashok

I loop through all the formats that you mentioned, the only problem is the “2nd March 2021”. This “nd” you need replace and let “2 March 2021”.

Any day with “nd”, “th”, “st” will be invalid formats.

All formats that you shown in the excel screenshot is valid formats and will be correctly converted just passing the culture info as I mentioned.

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.