Convert multiple date format into specific format

Help Studio
, ,
1

Hi,

I have an column in which multiple date format has present.
so I want to convert all format into specific format i.e. MM/dd/yyyy

Sample Data:

20/02/24
02/30/2024
13-08-2024
13/08/2024 so on…

2

Hi @itbuddy1

Use the below code in Invoke Code:

dt.AsEnumerable().ToList().ForEach(Sub(row) row("DateColumn") = DateTime.ParseExact(row("DateColumn").ToString().Trim(), {"dd/MM/yy", "dd/MM/yyyy", "MM/dd/yyyy", "dd-MM-yyyy"}, System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None).ToString("MM/dd/yyyy"))

Pass dt as In/Out as DataTable.

Hope it helps!!

3

@itbuddy1

check this thread as reference

4

hi, thanks for the solution but this will convert only some data.
for your reference I am provide more sample data.

can u provide me the solution accordingly

28-09-2024
12-09-2024
23-09-2024
26-09-2024
26-09-2024
25-09-2024
11-09-2024
26-09-2024
25-09-2024
02-09-2024
27-09-2024
10-09-2024
26-09-2024
27-09-2024
01-09-2024
27-09-2024
07-09-2024
27-09-2024
28-09-2024
28-09-2024
28-09-2024
28-09-2024

5

Hi @itbuddy1

Can you share the data through Excel file. I will help you with flow.

Regards

6

I already share the data in above chat these are in date format in an excel

7

Plz help me because this process is running in production

8

Hi @itbuddy1

Use Format Cells activity

Regards,

9

@itbuddy1

The image depicts an automation flow in Microsoft Power Automate Desktop, where an Excel file named "Excel1.xlsx" is being used to format cells in column C of Sheet1. (Captioned by AI)
The image depicts an automation flow in Microsoft Power Automate Desktop, where an Excel file named "Excel1.xlsx" is being used to format cells in column C of Sheet1. (Captioned by AI)611×688 21.5 KB

An Excel cell formatting interface is displayed, where cells in column C of "Sheet1" are being set to a custom date format "MM/dd/yyyy". (Captioned by AI)
An Excel cell formatting interface is displayed, where cells in column C of "Sheet1" are being set to a custom date format "MM/dd/yyyy". (Captioned by AI)855×353 16.3 KB

Regards,

10

Hi,

I tried above solution that is work but only work in which cell is in date type not in custom type
actually in this file some date type is date or some is custom type.

I have attached the actual data which I have received from user
Sample.xlsx (9.3 KB)

11

Hi @itbuddy1

Can you try below xaml

Sequence15.xaml (14.0 KB)

Output:

The image shows a column labeled "Proposed End Date" filled with various dates in September 2024 in an Excel spreadsheet. (Captioned by AI)
The image shows a column labeled "Proposed End Date" filled with various dates in September 2024 in an Excel spreadsheet. (Captioned by AI)318×691 5.88 KB

Regards,

12
13

Hi,

I am not able to open this XAML. This is showing invalid document.

14

@itbuddy1

The image depicts a UiPath sequence workflow that reads a range from "Sheet1," performs code execution to manipulate the data, and writes the resulting data to "Sheet2" in the same workbook. (Captioned by AI)
The image depicts a UiPath sequence workflow that reads a range from "Sheet1," performs code execution to manipulate the data, and writes the resulting data to "Sheet2" in the same workbook. (Captioned by AI)818×580 29.4 KB

dt.AsEnumerable().ToList().ForEach(Sub(row) row("Proposed End Date") = DateTime.ParseExact(row("Proposed End Date").ToString().Trim(), {"M/dd/yyyy", "dd-MM-yyyy"}, System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None).ToString("MM/dd/yyyy"))

The image shows a dialog box titled "Invoked code arguments" with a single argument named "dt" of type "DataTable" with an In/Out direction and a value of "DT". (Captioned by AI)
The image shows a dialog box titled "Invoked code arguments" with a single argument named "dt" of type "DataTable" with an In/Out direction and a value of "DT". (Captioned by AI)895×264 10.9 KB

Note: Enable Preserve Format Option in Read Range Workbook activity

image
image1451×758 48.5 KB

Regards,