Convert multiple date formats into a standard way

Dear Experts,

Good Day.

I have a situation where I need to convert multiple date formats into a standard way using LINQ.


Part Number Point Week Qty
ABC BNG 17/2021 390
ABC BNG 40-43/2021 390
ABC BNG 11.02.2021 390



Part Number Point Week Qty Week Date Week Format
ABC BNG 17/2021 390 4/19/2021 W202117
ABC BNG 40/2021 97.5 9/27/2021 W202140
ABC BNG 41/2021 97.5 10/4/2021 W202141
ABC BNG 42/2021 97.5 10/11/2021 W202142
ABC BNG 43/2021 97.5 10/18/2021 W202143
ABC BNG 11.02.2021 390 2/11/2021 D20210211

Appreciate your support



  1. This won’t be a good approach, Date format needs to be try.parse before conversion in order to make sure that it won’t fail.
  2. To standardize, multiple parsing needs to be done with a different type of date format which can’t be done into linq.
  3. Better approach would be to use a model that will try parse different formats and once done break parsing loop and move to next record.

Before forcing of doing with LINQ lets divide and conquer on the different building blocks.

Part: Week Info recognition
Week portion is not part of the date format patterns, so we have to handle different. also we do have different cases to approach: week, week range, date.

For recognizing it we can make use of Regex and Group names:

we do init a regex object with the pattern
we do iterate and execute the regex.match
in a switch we do calculate which of the group was firing and will use the group name for the fork

Other Steps
within the cases we can do the additional parsing, date calculation and target datatable population.

Getting date from WeekNo, have a look here for a similar sample as starter help:

For the part from above find starter help here:
manjesh_kumar.xaml (9.6 KB)


I think you can never get this 100% solid, if the sourcefields are human free-text input, since it’ll leave too many combinations to validate, especially if one value can be interpreted multiple ways.

if I gave this field 1-3-20 do I mean the week of March 1st 2020, January 3rd? Week 1, 3 and 20, week 1 through 3 in 2020?

You should expect a margin of error trying to decypher this. It depends of course on your process if this is acceptable or maybe even business critical.

A better solution would be to apply input validation on your entry forms. If that is excel it is certainly doable, or otherwise the input form in your front end, which usuually requires a few less accesible changes to the form. Even a proper workinstruction towards the people who provide the data might help.
In short; make it strict, dummy proof or robot friendly, anyway you want to call it :slight_smile: But please don’t make the robot guess a format. Structured data is a prime requirement of RPA. (At least untill AI matures a lot more)

I understand the last paragraph isn’t always within your direct influence as an RPA developer, but I’d put on my ‘business analyst’ hat and provide that feedback towards your requesters, to either change the input, or expect and accept a lot of process exceptions.

1 Like

Dear @ppr ,

Appreciate you valuable suggestion/feedback.

@Lakshay_Verma @Jeroen_van_Loon : Thank you guys for valuable inputs too.



1 Like

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