formatDateTime function does not correctly convert ISO 8601 datetime strings

Question

I have created a Logic App workflow that generates an Excel file with inactive guest users which is sent by email.

The workflow first performs the following query:

GET https://graph.microsoft.com/v1.0/users?$filter=signInActivity/lastSuccessfulSignInDateTime le 2024-05-30T11:05:28.8427628Z&$select=displayName,mail,userPrincipalName,userType,signInActivity

In the response the signInActivity object contains the following data:

"signInActivity": {
      "lastSignInDateTime": "2024-08-20T09:28:53Z",
      "lastSignInRequestId": "e820836a-0ea5-462d-8673-9d26eb6f7f00",
      "lastNonInteractiveSignInDateTime": "2024-08-27T11:04:54Z",
      "lastNonInteractiveSignInRequestId": "45cb7006-5ea2-4be1-873a-fe2866a89700",
      "lastSuccessfulSignInDateTime": "2024-04-15T06:05:02Z",
      "lastSuccessfulSignInRequestId": "c12ea9aa-0c71-4501-bf37-cb46acb60700"
    }

Then it filters all users where userType equals 'Guest'.

Next, it copies an existing (blank) Excel file and then appends the data into the Excel file by the use of the 'Add a row into a table' action. In this action I configured the DateTime Format parameter as 'ISO 8601'.

Also, in this action I use the following expression to format the string into the Dutch user friendly date format:

formatDateTime(items('For_each_-_Guest_user')?['signInActivity']?['lastSignInDateTime'],'dd-MM-yyyy HH:mm:ss')

The problem is now that this expression only works for datetime strings where the day value is higher than 12. Otherwise, the ISO 8601 string is returned from the expression.

Below two examples:

Input string A

"lastSignInDateTime": "2024-07-04T14:01:10Z"

Input string B

"lastSignInDateTime": "2024-08-20T09:28:53Z"

Input string C

"lastSignInDateTime": "2024-04-08T19:01:23Z"

Input string D

"lastSignInDateTime": "2024-04-25T09:03:55Z"

This is the output after the expression has been performed:

Output string A (wrong)

"lastSignInDateTime": "2024-04-07T16:01:10.000Z"

Output string B (correct)

"lastSignInDateTime": "20-08-2024 11:28:53"

Output string C (wrong)

"lastSignInDateTime": "2024-08-04T21:01:23.000Z"

Output string D (correct)

"lastSignInDateTime": "25-04-2024 11:03:55"

I am clueless what is causing this behavior. Does anyone know?

Thanks for all help in advance.

PS: The expected/desired outcome (output) is like examples 'Output string B' and 'Output string D'

Answer 1

I came to the conclusion that I interpreted it wrong.

I had to provide the ISO 8601 formatted timestamp in the Excel connector action 'Add a row into a table' and not perform the formatDateTime() function.

Excel then shows the dates in the desired format depending on the regional settings of Windows

Answer 2

Hi @Joost van der Linden Greetings! Thank you for posting this question here.

I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to accept the answer .

Issue:

DateTime formatting issue when converting a DateTime in Logic App using formatDateTime() function. Some of the date times are converted into the expected format but others don't

Solution:

To resolve this, provide the ISO 8601 formatted timestamp in the Excel connector action Add a row into a table and not perform the formatDateTime() function.

If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information.

I hope this helps!

If you have any other questions, please let me know. Thank you again for your time and patience throughout this issue.