Date format keeps changing in CSV file

[PriyavartRajain]

I have a flow that creates a CSV table, populates a CSV file with it and saves it in SharePoint. For the example's sake, I only have one column in the CSV file, which is the Date Received. I want the format of the date in this column to be YYYY-MM-DD, but no matter what I do in the flow, the resulting CSV always has MM-DD-YYYY format. The following is my flow: 

As you can see, I am only trying to create one column in the CSV table just to isolate this issue for now. The value for the Date Received header is: 

formatDateTime(body('Select')?[0]?['Date Received'],'yyyy-MM-dd')

(Yes, every row will have the same date, again, for simplicity. Later I'd like to change this such that each row has the date received found in each item of the Select output body, but that's a problem for later). 

Now I was expecting this to change the date value to year month date format, but it doesn't. 

I even do this in the select control just to be sure: 

Here the expression is: 

formatDateTime(item()?['dtmDateReceived'],'yyyy-MM-dd')

Now I know for sure that the date is being changed to the desired format in the flow, as can be seen here in the run history: 

 But in the CSV file the format automatically changes to a different one: 

I'd really appreciate it if someone could provide any insight into what's going on here. What am I missing? Also if I am able to fix this, how can I modify my value for the Date Received header so that it picks the date in each object from the Select output? 

[Redders]

Hi @PriyavartRajain,

I'm not too sure that there is a problem with your flow, although I'm prepared to be corrected.

I have created a notepad file with the values that your flow would output and when opened in Excel, it is showing the same as what you can see.

I believe what is happening is that Excel is auto changing the type of the data. When opening the same file using power query, you can see this happening as part of the applied steps

As you can see when I open the notepad at the side of the excel file, it is showing the different format

I hope this helps

Edit: Just to add, try opening the file in Notepad to see what your results actually are

[PriyavartRajain]

I see, all this time I have been trying to figure out what was wrong in my flow but I tried what you did and it indeed is something that Excel is doing. Thank you so much! Now I at least know that the issue isn't with my flow 🙂

[PriyavartRajain · Accepted solution]

Update: 

I was able to fix this by using the following formula in the select action for Date Received: 

concat('="',formatDateTime(item()?['dtmDateReceived'],'yyyy-MM-dd'),'"')

It forces excel to evaluate this formula when populating the CSV. Cheers! 🙂