Power Automate flow - output for date converted to wrong format

[Debs1]

I have a flow which is working great, apart from in the output to create sharepoint item it is formatting the date to an american format.  Site settings are set to region London and in the sharepoint list the date format is correct.  Something is wrong in the flow and I cannot see where. 

I can see in the input raw data the format is correct dd/mm/yyyy, but in the output it is mm/yy/yyyy Can someone help me please?  

[David_MA]

You can use an expression to convert it from one format to another. You can read about how to do that at How to customize or format Date and Time values in a flow - Power Automate | Microsoft Learn. You don't say where your current date values are coming from, but to use this method to transform the date, the original date must be in ISO8601 format, which is how SharePoint stores all dates.

[Chriddle]

Most of the time, such problems occur when a maker uses the formatDateTime() function and can solve the problem by removing this function. 😄

[Debs1]

Hi David_MA,

Thanks for your response.  I have changed the Power Automate settings to display language English and regional format UK - but the output in my flow is still showing in US format. Might it take time for the settings to update? Could this be the root of my problem? Yes the date is coming from a SharePoint list which has the settings set to region London and manually adding an item to the list shows the correct date format.  It is the dates exported by the Power automate get sharepoint item that are shown in the output incorrectly. I have the same error when the flow creates a new item in another list, again the sharepoint list settings are correct and manually adding an item shows the date correctly, but when an item is created by the flow it inserts the date in the US format. I am a novice, building lots of flows and learning lots, but I do not understand where I should be put the expression to convert the date format? I've attach an image of the flow which might help.

[Chriddle]

Your Sharepoint's regional settings  don't matter. In PA a date is an ISO-8601 formatted string.

PA can also handle the American date format, but this is more confusing than helpull and you probably should not use this (IMHO).

[Debs1]

Hi Chriddle,

Are you saying I am correct to change the PA settings to UK format?

Thanks...

Debs

[Chriddle]

It simply does not matter, a date in PA is an ISO-8601 formatted string.

Typically, connectors return dates in this format and expect this format as input.

So do the date functions that you use in PA expressions.

Therefore, you should not use formatDateTime() to mess with date formats 😉

[Debs1]

Hi Chriddle,

Thanks for that information - are you saying i should remove the expressions I have in the initialize variables? I set these so that I could check if a form response had been received for the current month - I'm a bit lost here now. Images of initialise variables and condition included.

[Chriddle]

Without seeing more parts of your process, I can't say exactly what you should do.

In general, if you try to insert an expression like formatDateTime(utcNow(), 'dd/MM/yyyy') into a Sharepoint list, you will get nonsense or the flow will fail. This depends on whether the current day of the month is over 12 or not.

Furthermore, something seems to be wrong with the date condition.
The part "Date Su... contains Month" is ambiguous because the day part of the date could also return true here

[Chriddle]

An examle how to get all items of the current month:

Filter over the list items: outputs('Get_items')?['body/value']

Check if Now2 (a date column in the list) starts with utcNow's year and month:

item()['Now2'] starts with utcNow('yyyy-MM')

Now2 is a date in my list and therefore (in PA) a string formatted like this (ISO-8601😞 '2024-01-01T08:29:00Z'.

That's why you can use string functions like startsWith(), equals() etc.

[Debs1]

Hi Chriddle,

Thanks so much for your responses - I was just working on a filter array like the one you have shown, but I also need to filter by another column - the email address of a user.  If I use two filter arrays how do I combine the answers?  Thank you!

[Chriddle]

Then you have to edit the filter condition in advanced mode.

In this example, I'm filtering for items with a date within the current month and the author's email address is "jim@example.com":

@And(
	startsWith(item()?['Now2'], utcNow('yyyy-MM')),
	equals(item()?['Author']?['Email'], 'jim@example.com')
)

"@" characters don't work well with the forum software 😬.
"and" after the @ must not have capital letters in the expression above !