Unanswered

Dates passed to Excel changing format after 12th of the month

(0) Share

Report

Posted on by Community Power Pla...

I'm having a problem passing dates from FLOW back to an excel spreadsheet and retaining the formating. I need the dates to be UK format as I have other FLOWS that will read from the spreadsheet and filter by date.

I've checked Sharepoint region setting is set to UK, my local settings are UK. Excel CELLs have been formatted to display UK date.

When running FLOW, i can see that the date formats being passed to excel are correct.

The weirdest thing is, dates from 1st-12th of the month are passed in US format ie MM-dd-yyyy then anything beyond 13th of the month is UK format dd-MM-yyyy.

Am i missing something here? Is there something I'm doing wrong???

Categories:

General topics

I have the same question (0)

All responses (6)

Answers (0)

DeepakS 2,301 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Hi @Anonymous

Could you please recheck because if i see the output excel in screen capture that you shared date looks to me in UK format, like for an example row 5 ,7 8 and 9 dates are in UK format
03/10/2020 - 3rd October 2020
03/09/2020- 3rd September 2020

Let me know if i did not understand you question.

-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

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

Like (1)

Report
Copy link

Link copied!

Thanks for the reply - i've changed the Cell format within excel to display dates as dd MONTH yyyy - so in this case, they're correct. HOWEVER, the actual system date, or the date that was passed to excel was 09/03/2020 or 10/03/2020 which in day MONTH year format should be 9 March 2020 or 10 March 2020, and is instead 3 September 2020 or 3 October 2020.

I think i have found where my error was. Using formatdatetime(utcNow(), 'dd/MM/yyyy') and passing the resulting output to Excel will give me the wrong date format in Excel. After changing how I format the date in the function, i have an acceptable solution:
FormatDateTime(utcNow(), 'yyyy/MM/dd')

I am now going through all my flows to check all the filters i have to make sure the correct date is being passed back to Excel and vice-versa!

Was this reply helpful? Yes No
pmasood 4 on at

Like (0)

Report
Copy link

Link copied!

Hello, I too have the same issues where the date format is taking mm/dd/yyyy from day 1st to 12th, post 13th to 31st it is taking the format of dd/mm/yyyy. Could you please advise the fix for this issue actually I need all days to reflect in UK format.

Was this reply helpful? Yes No
pmasood 4 on at

Like (0)

Report
Copy link

Link copied!

Hello, I too have the same issues where the date format is taking mm/dd/yyyy from day 1st to 12th, post 13th to 31st it is taking the format of dd/mm/yyyy. Could you please advise the fix for this issue actually I need all days to reflect in UK format.

Was this reply helpful? Yes No
Chriddle 8,515 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Just don't use the function formatDate() in your flow !

In PA dates are ISO-8601 formatted strings and the Excel connector also expects this format for inputs.

If you format your date, it's probably not a date anymore (except you format it accordingly)

Was this reply helpful? Yes No
Chriddle 8,515 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Not that strange: If Excel recognizes a value as a date, it displays it accordingly, otherwise it just displays the string.

Was this reply helpful? Yes No