Column format Change

[Shashi123 (Shashi)]

Hi Team,

I want to sort a column in Excel which has datetime value. I used read range. Got the excel data into datatable and than used sort datatable activity The table got sorted in the way i want but the datetime format got changed from 12 hour to 24 hour format. Need the datetime column in same format as it was at the start.

[supermanPunch (Arpan)]

@Shashi123

i want but the datetime format got changed from 12 hour to 24 hour format.

Hi @Shashi123 ,

Could you maybe confirm if the Data got changed before Sort or only after Sort Datatable was performed ?

[Shashi123 (Shashi)]

Hi,

Yes the format hanged before sorting the datatable.

[Gokul001 (Gokul Balaji)]

Hi @Shashi123

Can you check the following thead

https://forum.uipath.com/t/date-format-issue/543817/2?u=gokul001

Regards
Gokul

[Shashi123 (Shashi)]

It says link not found

[supermanPunch (Arpan)]

@Shashi123 ,

Give a first try by Enabling the PreserveFormat option in Read Range activity and perform the remaining actions.

We can then check with the Format Cells activity if you are comfortable in using the Modern Design activities.

DateTime.ParseExact the whole Column without looping

Hi @arthurang , If the Target Goal for you is to represent the dates in Excel Sheet Column in MM/dd/yyyy format, maybe you could use the Format Cells Activity if you have the latest versions in place. [image] You would require to know which column the Date column is present (A or B or C, in my case it is J), then we can use the Range as J:J. If we do not know the position of the column in the Excel sheet too, we can dynamically be able to find it as well. Input : [image] Output: [image] …

[Gokul001 (Gokul Balaji)]

Hi @Shashi123

Check out this link

https://forum.uipath.com/search?q=change%20cell%20format%20%40Gokul001

https://forum.uipath.com/search?q=change%20cell%20format%20%40Gokul001

REgards
Gokul

[Nitya1 (Nitya Tomar)]

Hi @Shashi123

Before sorting the DataTable, create a new column to store the formatted datetime values in the desired format. You can use the Add Data Column activity to add a new column of type String to the DataTable. Iterate through each row in the DataTable using a For Each Row activity. Inside the loop, use the Assign activity to assign the formatted datetime value to the new column.
You can use the ToString method with a specific format string to achieve the desired format. For example, if you want to convert a datetime value to a 12-hour format with AM/PM indicator, you can use the following expression:

row(“NewColumn”) = Convert.ToDateTime(row(“DateTimeColumn”)).ToString(“MM/dd/yyyy hh:mm:ss tt”)

Replace “NewColumn” with the name of the new column you added and “DateTimeColumn” with the name of the datetime column in the DataTable.

After assigning the formatted datetime values to the new column, use the Sort Data Table activity to sort the DataTable based on the new column. Make sure to select the correct sorting order (ascending or descending) and choose the new column as the sorting key.

Thanks!!