Format multiple Excel date fields of a column

[Wilson4Ever]

Hey PAD Enthusiasts 😊,
when reading an Excel table in which there are several date fields, the date is read an write back in a new Excel table in this format:

How do I manage to rewrite the complete date fields into the following format yyyy/MM/dd ?

I did it with this method, but of course this only works for one line.

For each method did not work for me either. Does anyone have a best practice case?

Greetings from Lake Constance

Rookie PAD Chris

[Ankesh_49]

Could you please try getting the row count and use it in for each. It should solve your purpose. 

[rolign · Accepted solution]

If I understand your case correctly, this should be working (copy-paste in the flow and adjust as necessary)

Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''test.xlsx''' Visible: True ReadOnly: False Instance=> ExcelInstance
Excel.ReadFromExcel.ReadAllCells Instance: ExcelInstance ReadAsText: False FirstLineIsHeader: False RangeValue=> ExcelData
SET ExcelRow TO 0
LOOP FOREACH CurrentItem IN ExcelData
SET Date TO CurrentItem[2]
Text.ConvertDateTimeToText.FromCustomDateTime DateTime: Date CustomFormat: $'''yyyy/MM/dd''' Result=> FormattedDateTime
Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: FormattedDateTime Column: $'''C''' Row: ExcelRow
Variables.IncreaseVariable Value: ExcelRow IncrementValue: 1
END

[Ankesh_49]

Yes. This should work.
Let me know if you encounter any error.

[Wilson4Ever]

Thank you very much for the support.

We had the following Excel file. An export from an application that provides employee absences.

These need to be read into another application and in the following format.

This PAD Flow now implements this perfectly.

We are really very happy with it. Our first RPA that saves 10 min of working time every day.