Date format issue from FLOW to Excel - date changes dd-MM-yyyy (correct) to MM-dd-yyyy (NOT WANTED)

[Anonymous]

Doesn't matter what I do, every time I send a string formatted 'dd-MM-yyyy' to an excel table, it changes the date format in EXCEL to MM-dd-yyyy. 

I've collected data from a form, and used Submission Time to get the date. Using the following: 

formatDateTime(body('Get_response_details')?['submitDate'], 'dd-MM-yyyy')

OR

string(formatDateTime(body('Get_response_details')?['submitDate'], 'dd-MM-yyyy'))

or 
formatDateTime(utcNow(), 'dd-MM-yyyy')

I run FLOW I get the following CORRECT output - so FLOW is working fine.

However, EXCEL always displays incorrect format (MM-dd-yyyy) (IGNORE lines 20/21 - i've had to manually enter the dates there to confirm FLOW was reading the file correctly) 

Column is set to format cells with UK date format. 

Using Submission time only will give the correct date format (see below), however reading back into the flow becomes problematic due to the timestamp after the date.

It appears to be the way Excel is handling the date. 

Can anyone help me or point me in the right direction?

[SteveYao]

Hi @Anonymous 

What's the locale setting for your OS?

Another workaround is to add a single quote before the date output, like below

[Anonymous]

Thanks for the reply.

my OS location settings are UK. 

Placing an single quote in front of the date output does have a result in excel but i can't manipulate the date in excel as its treated as a string.

excel displays the date as

08-03-2020

The problem with that is i have another flow that is triggered at a later time to access the same table and pull data based on date. 

What is weird, is that this problem started when I used the submission date passed to my flow from a Form. Prior to that, I had used a Date Selector within the form which had no problems passing the correct date format back to excel.

[fchopo]

Hello @Anonymous 

Just one question. Do you have the same problem if you use Excel Online to open the file instead of Excel Desktop app?

[Anonymous]

I do - same problem if its online, or if its on desktop.

I've checked my onedrive region settings and they're UK as well. 

Its quite frustrating, I have just set up another new test with a very simple flow to test: 

(I changed the name of the Initialise Variable functions between screenshots) 

Dates are in correct format....

Dates in excel - changed again. If i check the cell format - the location is set to UK.

[Anonymous]

I added a further column (updated date) in the excel table that is being passed data and used the following function to pass the date:

addDays(body('Get_response_details')?['ra7027232de9c4a86aa564f9493720ee3'],0,'dd-MM-yyyy')

I still have the same problem. 

further testing has produced a weird result.

Dates including and beyond 13th March 2020 are formatted correctly (local region settings) when passed on to Excel, whereas dates earlier are formatted US.

What is going on here??

[SteveYao]

I think because if earlier than March 13rd, 2020, for example March 12nd, system may think 12 is not the day, it may treated as Month December.

[Anonymous]

I'm having the EXACT same problem, so frustrating! Was a fix ever found for this issue?

[Anonymous]

I think I've got a workaround that preserves the ability to use data in Excel. I set the output from Flow as dd-mmm-yyyy but with the cells in Excel formatted as dd-mmm-yyyy.

[Chris81]

I've found a solution that works.

The problem is that Excel expresses dates as an integer with the "date 0" being 1900-12-1. Excel appears to have issues when Flow changes the format to put days first. To get the correct date entered we need to calculate the difference between dates.

In PowerApps, I have a label showing the calculated difference

DateDiff(DateValue("31/12/1899"),Today()).  I then send this number into excel as the date.

Doing this calculation is trickier in Flow but here is what I did.

Compose (Data Operation) and put an input of:

Sub(div(ticks(DATE1), 864000000000),693593)

DATE1 - is the dynamic content with the date you want to go into excel 864000000000 - being the number of ticks in a day.

693593 - being the number of ticks since 01/01/1601 (the day zero date for ticks)

Then just use your Flow to put this calculated value into the date column of your excel spreadsheet and let excel do the rest.

Hope this all made sense.

[jay_june]

As above, Excel stores dates as integer, so you can get around this by calculating how many days you have from the excel start date.

I would use

div(div(mul(sub(ticks(formatDateTime(body('Get_response_details')?['submitDate'], 'yyyy-MM-dd')), ticks('1899-12-30')),100),1000000000), 86400)

ticks converts a date to milliseconds from epoch time, so we're subtracting excel start date ticks from your required date ticks. Then we do some conversion to change that to days in integers.

Format your excel cell to dd/mm/yyyy and it should work fine.

[An4tomic1]

By far the cleanest solution as it only modifys an already in use parameter:

In flow just had to change the "formatDateTime" string:

Bejore:

formatDateTime(body('Hora_actual'),'dd/MM/yyyy')

After:

formatDateTime(body('Hora_actual'),'dd/MMM/yyyy')

The result is the value sent to excel changes as shown on the example:

Then in the excel there's nothing to change as defaults works just perfect with that input values.

Thanks!

[OwenDearing]

I agree writing in dd/MMM/yyyy format works, but I also find writing to the Excel file in US format works: MM/dd/yyyy

[wolfgang001]

Is there anyone who can help me with this?

Problem encountered:

Date format of my sharepoint list is not the same when it writes to my excel for business online.

This is the date and time format in my SharePoint list which should also reflect the same format in my excel.

This is the code that I've written in my Flow

While this is the output when it writes to my excel.

Hope someone can help me with this.

Thank you in advance..

[jay_june]

Since you are saving the date to excel as a string, use the formatDateTime() function.

[wolfgang001]

Hi Jay_June,

Thank you for your help. However, I'm still having a hard time following the link you've sent. I'm not a programmer, instead I watched videos in YB just to learn this Program.

Action 1:

This is what I did

and this is the result in my excel is this.

Action 2:

Result:

Action 3:

Add some 'mm/dd/yyyy hh:mm'

formatDateTime(triggerOutputs()?['body/StartDate']),'mm/dd/yyyy'

Result:

"The expression in invalid"

Could you help me sent the specific code for this?

Thanks in advance.

[jay_june]

Hey, it looks like you've misplaced a bracket - it should be at the end.
formatDateTime(triggerOutputs()?['body/StartDate']),'mm/dd/yyyy'

formatDateTime(triggerOutputs()?['body/StartDate'],'mm/dd/yyyy')

[wolfgang001]

Hi Jay,

The code is now working😀. However, the time is not the same as expected.

This is the time format from my SharePoint list that I need to reflect in my excel.

and the result in my excel is this.

This is the code I've used.

formatDateTime(triggerOutputs()?['body/StartDate'],'MM/dd/yyyy HH:mm tt')

The time should be in 24hrs.

[jay_june]

It looks like the data is stored in UTC time and the difference in time is because of timezones. try convertTimeZone()

convertTimeZone(triggerOutputs()?['body/StartDate'],'UTC','MM/dd/yyyy HH:mm tt')

[wolfgang001]

Hi Jay,

Upon testing this is the result.

convertTimeZone(triggerOutputs()?['body/StartDate'],'UTC','MM/dd/yyyy HH:mm tt')

Result