Answered

Format multiple Excel date fields of a column

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

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

Categories:

Power Automate Desktop

I have the same question (0)

All responses (4)

Answers (1)

Sort by

AS-06081019-0 1,738 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Verified answer

rolign on at

Like
a
(0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
AS-06081019-0 1,738 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No