How to change date time format of an excel cell

hello

I would like to know how to change the format of an excel collumn where the cells are configured with a date format:

dd/MM/yyyy hh:mm:ss

to

dd/MM/yyyy ,

I have a pivot table that doesn’t work properly because of the hours.

Question 2: Is it possible, when I copy excel data into a tab that feeds pivot tables, for it to update them automatically?

thanks

@frederic-f.bonnet

  1. Please use format cell activity

image

  1. Depends on what you want to correct we can suggest

cheers

Sometimes on Excel if you use format function to change the format the information is again present in the cell. Even if it seems easy , in fact it is not easy at all.

Question 1: Use the follow below steps to achieve the output:

  1. Select the column containing the date values that you want to modify.
  2. Right-click on the selected column and choose “Format Cells” from the context menu.
  3. In the “Format Cells” dialog box, go to the “Number” tab.
  4. Select “Custom” from the category list on the left side.
  5. In the “Type” field, enter the desired date format: “dd/MM/yyyy”.
  6. Click on the “OK” button to apply the changes.

Question 2:
Depending upon the requirement you need we can suggest an solution.

Hope it helps!!
Regards,

1 Like

Hi @frederic-f.bonnet

Try this-

  1. Add a “For Each Row” activity to loop through each row of the DataTable obtained from the “Read Range” activity.
  2. Assign Activity: Inside the loop, add an “Assign” activity to modify the format of each cell in the date column.
  • In the “To” field of the Assign activity, use the following expression:
    row("ColumnName") = Convert.ToDateTime(row("ColumnName")).ToString("dd/MM/yyyy")Replace “ColumnName” with the actual name of the column containing the date values.
  1. Write Range: After the loop, use the “Write Range” activity to write the modified DataTable back to the same Excel sheet.
  2. Refresh Pivot Table: To update the pivot table automatically, you can use the “Refresh” method of the pivot table object.
  • Find the pivot table in the Excel sheet.
  • Use the “Invoke VBA” activity in UiPath and execute the following VBA code to refresh the pivot table:
Sub RefreshPivotTable() ActiveSheet.PivotTables("PivotTableName").PivotCache.Refresh End Sub

Thanks!!

1 Like