Normalizing Date formats

[Akhlesh_KumarJi]

HI, BOT is processing approx. 5 excel files per day. Each file is having structure data, one column is date field. Date field is not consistent in all the files. Possibility:  File 1: Date format - mm/dd/yyyy File 2: Date format - dd/mm/yy File 3: Date format - DD-MM-YYYY File 4: Date format - MM-DD-YYYY File 5: Date format - dd-MMM-yyyy We are using "FormatDateTime (Date, DateFormat)"  to get date in one unique format. But since date filed format varies for each file, BOT may produce wrong result or may throw an exception. Is there a way BOT can sense date format from excel file and parse it into desired format without any error? Thanks.

[SachinJagdale]

You can use different regex patterns to identify correct date from the this string and then try to format it.

[Akhlesh_KumarJi]

Thanks for reply. I am not sure by using regex BOT can sense the correct Date format in Excel. For example: File 1: Date field = 9/6/2018 (assume format is dd/mm/yyyy) File 2: Date field = 9/6/2018 (assume format is mm/dd/yyyy) In this case, by using regex BOT may produce incorrect result in any one case. can you help with little more understanding how this can be achieved using regex?

[Joshi_KumarA_V]

Does the date column contain date from a range of dates? or from current month? If it is from random month, it is impossible to differentiate cases where 9/6/2018 and 6/9/2018. If they are from the same month, we can use an apporach to solve that.