Formatting time column in data table from AM / PM to HH:MM:SS


Is there a way to format columns within a data table? I used the Read CSV activity to import data from a csv table into a data table in my automation and now I’m trying to format 4 time columns.

The time columns are currently in AM / PM format “1:06 PM” and I’m trying to format them within the data table to HH:MM:SS format “13:06:00”.

Any help would be appreciated, thank you!


Check below post for your reference

Hope this will help you



Thank you for the response, this is helpful. I’m trying to format the entire column so I used the assign activity. Within the assigned I called out the column I want to format but I’m receiving an error. I’ve included my assign activity along with the error below.

dtRatingsData.Columns(24) = Datetime.Parse(“hh:mm tt”,new system.Globalization.CultureInfo(“en-US”)).ToString(“HH:mm:SS”)

Error Message - Cannot assign from type ‘System.String’ to type ‘System.Data.DataColumn’ in Assign activity ‘Assign’.


Use For Each row activity

Inside that assign activity as

dtRatingsData(24) = Datetime.ParseExact(row(24).ToString,“hh:mm tt ”,new system.Globalization.CultureInfo(“en-US”)).ToString(“HH:mm:ss”)

Try as above

Hope this may help you


Datetimes, times etc do not have formats. They store values. You format them as you’re outputting them, with .ToString(format)

You are trying to assign to the entire column, instead of a row AND column.

Columns(24) is a DataColumn object, not a “cell” that stores a value.

dtRatingsData.Rows(x).Columns(y) = Datetime.parse…


I used the for each row activity and I’m still getting the following error.

Error - Argument ‘Value’: Compiler error(s) encountered processing expression "DateTime.ParseExact(row(24).ToString,“hh:mm tt”,new system.Globalization.CultureInfo(“en-US”)).ToString(“HH:mm:ss”). Value of type ‘String’ cannot be converted to System.Data.DataColumn’.



Instead of dtRatingsData.Column(24), write as dtRatingsData(24)

Hope this may help you



With that tweak it is now showing the following error message.

Again I appreciate all the help!

This is incorrect. It’s not an array. It’s a datatable, so you have to reference the row and column. dtRatingsData.Columns(24) is a datacolumn object, not the value in a row and column.

To get the value from a particular row and column, you have to do…


x = row index
z = column name or index

But since he is already looping through the rows, the row is represented by the “row” variable set up in the For Each Row. So the reference for a column would be row.Item(column name or index) and then you append the .ToString to get the value.



Thanks for the correction. That got rid of my error but when I go to run my bot in debug mode it seems to be getting help up at my assign function.

I’m assuming it’s something within my time conversion function that is causing this hold up. See below.

DateTime.ParseExact(row.Item(24).ToString,“hh:mm tt”,new system.Globalization.CultureInfo(“en-US”)).ToString(“HH:mm:ss”)


we can break it down to the building blocks:
for the conversion in general:

an implementation could look like this:
sample data:



find starter help here:
ppr_DTUpdateBox_KevinLegendre.xaml (9.0 KB)


@ppr okay perfect that worked for me, appreciate the help!

@ppr One thing I’m finding when I run my bot it takes very long time for it to run through the for each row and assign activities. I’m only formatting four columns within my data set and my sample data set only contains about 40K rows. The first time I ran it took over a half hour. If I comment out this activity it only takes about a minute. Is it normal for this activity to take this long with this amount of rows? If so is there a more efficient solution?

There is not a more efficient solution. More rows means more time. Keep in mind that if you’re using Debug mode, it runs more slowly.

1 Like

As mentioned debug mode is much slower as run mode. You can try parallelization e.g. with Parallel for each Activity (iterate over the datable: YourDataTable.AsEnumerable, set typeArgument to Datarow). Endure for this, that only the core task (update column values) is implemented and comment out any other unneeded activities e.g. log message…

Also have a readin to the topic here:

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