Converting String to Date w/ Multiple Formats

As background, I am pulling a date from a report online using ‘Copy Selected Text’. I am then converting the date using Date Parse Extract (see below) and then adding days to that (e.g., 7 days) and then taking that new date and update the report field.

Here is the issue: If the date is March 1, 2019, it is displayed on the site as 3/1/19 which works fine for the formula. The problem arises where I have different date formats which will make the date parse invalid. Here are all 4 possible date formats (with examples):

MM/dd/yyyy (10.11.2019)
MM/d/yyyy (10.1.2019)
M/dd/yyyy (3.10.2019)
M/d/yyyy (3.1.2019)

I need to be able to have the bot choose the correct format to be able to convert and then add days, otherwise the formula will not recognize and return an invalid format error.

Any suggestions on how to make this formula dynamic or other solutions?

Date.ParseExact(closeDate,“M/d/yyyy”,System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat)

Hi.
Well, if you use .TryParseExact() it will return a boolean value. So, you can use that as a condition before using .ParseExact().

To go further, if you have the formats in an array, you can use some LINQ.
For example,

arr = {"MM/dd/yyyy","MM/d/yyyy","M/dd/yyyy","M/d/yyyy"}
datevalue = DateTime.ParseExact(closeDate, arr.Where(Function(f) DateTime.TryParseExact(closeDate,f,System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat)).ToArray.First,System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat)

I did not test this, so hopefully it works. Let me know if you see any errors.
Essentially, this LINQ runs through the array of formats and filters it to the one that is True when using .TryParseExact(). Then, uses that string as the format for your ParseExact.

EDIT: Also, keep in mind if the date string is not in any of the formats, the LINQ example will throw an error. In that case, you would need to pull out the .Where() line of code and assign that to another array variable, so you can check its .Count to make sure it matched any of the formats prior to parsing it.

Regards.

2 Likes

Hi,
You can try the following Date.TryParseExact(stringVariable,“MM/dd/yyyy”,Nothing,Globalization.DateTimeStyles.None,Nothing).ToStringt

Use decisions for all the formats and change the format accordingly M/d/yyyy or MM/d/yyyy or M/dd/yyyy by this way it will give you Boolean result and based on that you can proceed further to add days to the given date

Please mark this as solution if this was correct or will try other way to solve the issue

Regards,
Pavan H.

Hey Clayton,

Thanks for the quick response. Here’s the error when I try that method.

Sorry, there were some other arguments needed. Try adding Nothing a few times on the end like this:

It shows it needed “style” and “result” as arguments, but don’t think you need them, so Nothing should be good.

Does it really fail with M/d/yyyy? All of the above values match that. Double MM and dd are for formats with leading zeroes, the ones you have should be handled just fine.
IIRC also the wider formats (MM and dd) treat leading zeroes as optional, so that alone should work as well.

In any case, there is also the overload that takes a string array and parses if at least one matches:

1 Like

Yeah, if you use ParseExact(). It won’t with just .Parse though.

You’re right. You can simply just place that array in the ParseExact, without needing .TryParseExact.

arrFormats = {"MM/dd/yyyy","MM/d/yyyy","M/dd/yyyy","M/d/yyyy"}
datevalue = DateTime.ParseExact(closeDate, arrFormats, System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat)

Should probably still use .TryParseExact though before to make sure it’s a date format.

arrFormats = {"MM/dd/yyyy","MM/d/yyyy","M/dd/yyyy","M/d/yyyy"}
If DateTime.TryParseExact(closeDate, arrFormats, System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat, Nothing, Nothing)
  datevalue = DateTime.ParseExact(closeDate, arrFormats, System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat)

@jsrote I say do that instead.

4 Likes

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