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
ppr
(Peter Preuss)
October 26, 2021, 12:33pm
2
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
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?
ppr
(Peter Preuss)
October 26, 2021, 12:46pm
4
can you please answer the the question related to the locals / current culture?
ratata
October 26, 2021, 12:49pm
5
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…
ratata
October 26, 2021, 1:06pm
8
if(DateAsStr.Contains(“Sept”))
{ DateAsStr = DateAsStr.Replace(“Sept”,“Sep”); }
//then do your conversion
ppr
(Peter Preuss)
October 26, 2021, 1:10pm
9
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:
so we can also parse:
DateTime.ParseExact("17Kkk2020","ddMMMyyyy", CustomDTF)
1 Like
ImPratham45
(Prathamesh Patil)
October 26, 2021, 1:18pm
10
Hi @Omkar_Govalkar ,
Welcome to community!!!
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))