Stuck on parsing date with different format

I have dates in excel in format as
10_Oct_2012
5_Sept_2015
01_Jan_2012

After reading excel
Using for each row to iterate…
I’m trying to parse the date using
DateTime.ParseExact(DateAsStr, “d_MMM_yyyy”, System.globalization.cultureinfo.invariantvulture)

It can’t parse the date 5_Sept_2012
Because it is in format d_MMMM_2012

How could I overcome this problem…

Thanks in advance for help.

Regards,
Omkar Govalkar

we can parse multi formats with one ParseExact:

arrFormats | String( ) = {“d_MMM_yyyy”, “d_MMMM_yyyy”}

DateTime.ParseExact(item, arrFormats, CultureInfo.InvariantCulture, DateTimeStyles.None)

ensure System.Globalization is added to the imports.

kindly note: MMMM will expect September not Sept

grafik

which locals (current language) is in use at your end?

If MMMM is going to expect September but it is present as Sept so how to handle this Sept case?

can you please answer the the question related to the locals / current culture?

One way to do it is to do a try catch, if it fails then in the catch scope use the other format. Or if your data only has sept not working then just do a string replace.

English (United States)
Is this what you were asking for…
Sorry if I’m wrong, new at this…

No can’t ignore the date that doesn’t match the format…

if(DateAsStr.Contains(“Sept”))
{ DateAsStr = DateAsStr.Replace(“Sept”,“Sep”); }

//then do your conversion

You can check different options:

  • Correction on string base for the month
  • Approache e.g. with Regex for ectracting only first 3 Month letters
  • Controlling the source sending your the data

Or also:

Parsing with a custom Set of Abbreviated Month names:
Variables:

Assigning the FakeMonths to the CustomDTF:
grafik

so we can also parse:

 DateTime.ParseExact("17Kkk2020","ddMMMyyyy", CustomDTF)

grafik

1 Like

Hi @Omkar_Govalkar ,

Welcome to community!!! :slight_smile:
Please try below solutions:

var_Date= Your date string

cDATE(var_date.split("_"c)(0)+"-"+var_date.split("_"c)(1).Substring(0,3)+"-"+var_date.split("_"c)(2))