cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Wilson4Ever
New Member

Format multiple Excel date fields of a column

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:

 

Wilson4Ever_0-1658917082880.png

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.

 

Wilson4Ever_1-1658917188813.png

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

 

Greetings from Lake Constance

Rookie PAD Chris

1 ACCEPTED SOLUTION

Accepted Solutions
rolign
Advocate III
Advocate III

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

View solution in original post

4 REPLIES 4
Ankesh_49
Super User
Super User

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

rolign
Advocate III
Advocate III

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
Super User
Super User

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

Thank you very much for the support.

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

Wilson4Ever_1-1659522728498.png

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

Wilson4Ever_2-1659522772710.png

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.

Wilson4Ever_0-1659522654687.png

 

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Community Calls Conversations

Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (1,988)