Column changes format after power automate process

[Amirin]

Hello guys,

Who amongst you know this problem?

I just want to copy the column with a date and time in it. I did not do anything, but when I processed it in power automate. The data changes its format.

This is my raw file.

This is my dumping file. the first 2 rows is originally there, that is the result i need. 

This is my flow with a perfect result. but when put in dumping file. it changes. 

[jbishop]

This can often be an issue of Excel, my guess is that Excel seeing the values in Rows 4-7 as dates and Rows 2 and 3 as Text. Which means that the formatting is being automatically converted to the standard Excel Dates Format once a new row is added.

Try Selecting all of Row A and setting the Cell Format To General rather than Date, and I believe that should fix it.

Please let me know if that solves your issue,

Cheers,

jbishop

[Amirin]

Hi.. thank you for your suggestion.. I did what you said.

But sadly, it did not do anything. The column switches automatically from GENERAL to CUSTOM after automated. 

[jbishop · Accepted solution]

Ohhh I mistook what happened the first time.

So your flow is putting the Date value into Excel as a Date Value, meaning it will default to the mm/dd/yyyy hh/mm format since that is the standard format for All Dates. So, from what it looks like, you are getting the data you are looking for, just not in the correct format.

Since you want your data in a format besides what Excel will default Dates to, here are the steps you can take to set your own custom format for Excel

1. If you are in Desktop for the Web/Teams, Open Excel in Desktop (This can be done by clicking the Dropdown with Editing in it near the top right, and selecting 'Opex Excel In Desktop')

2. Select The Entire Column You want a Custom Format in

3. Click on the Data Type Dropdown (The Dropdown where it says 'Custom' on the 'Home' Tab).

4. Select The 'More Number Formats' Option.

5. Select Custom in the 'Category' Section of the 'Number' Tab.

6. Right Below where it says 'Type:' Insert the Code 'yyyy-mm-dd hh:mm:ss' (View Picture Below for Step 5/6)

This should set the values to the format you are looking for.

If you have any further issues, please let me know

Cheers,

jbishop

[wskinnermctc]

@Amirin in your "Add a row into a table" screenshot it shows the date as

"date": "2023/07/29 06:55:10"

So the format "yyyy/MM/dd hh:mm:ss"

When is this date getting formatted into "yyyy/MM/dd hh:mm:ss" in your flow? Are you putting it into that format with an expression or date step?

Most dates are going to appear in a flow in the format "yyyy-MM-ddThh:mm:ss" by default.

So how is it getting changed into "yyyy/MM/dd hh:mm:ss"?

[Amirin]

Hi..

I accept your answer as solution..

It does solved the problem, but it does not work on online excel or excel 365 because online excel does not allow entering your own custom date.

What I did was download the online excel and inputted the custom date. and reupload again to sharepoint.

[jbishop]

Glad to hear you found a solution to your issue.

Happy to help!