formatDateTime to ISO 8061 format for Create Event (V2)

[fordwayrichardg]

Hi, all.

I have created a flow that takes data from an email and converts it to a calendar entry. The email formats the date using UK format (dd/MM/yyyy hh:mm:ss). I have used various Compose and Split functions to break the email body into the constituent parts resulting in the Date and Time being held in an output called Compose_End_Date_and_Time_(Split). 

In the Create Event (V2) function, I originally added outputs('Compose_End_Date_and_Time_(Split)')[1]) to the End Date and Time parameter and although this works if I enter a date such as 02/12/2017 11:59:00 it ends up going into 12th February instead of 2nd December. Enter a date like 27/11/2017/ 17:00:00 and it fails entirely.

I have tried to update it to use formatDateTime and reformat the incoming date and time value to UK but it won't work. Can somebody point me in the right direction please?

[efialttes · Accepted solution]

Hi!

You can keep on splitting the date (first separating date from time; then separating day/month/year and finally rebuilding the date in the desired format), but let's hope Microsoft Flow Support team can lead us into a more reasonable solution.

If not, this is my ugly proposal, taking the example 27/11/2017 17:00:00

split(outputs('Compose_End_Date_and_Time_(Split)')[1]),' ')[0]  will provide 27/11/2017

split(split(outputs('Compose_End_Date_and_Time_(Split)')[1]),' ')[0],'/') will provide the following array ["27","11","2017"]

concat(split(split(outputs('Compose_End_Date_and_Time_(Split)')[1]),' ')[0],'/')[1],'/',split(split(outputs('Compose_End_Date_and_Time_(Split)')[1]),' ')[0],'/')[0],'/',split(split(outputs('Compose_End_Date_and_Time_(Split)')[1]),' ')[0],'/')[2]) will provide 11/27/2017

I haven't tested the expressions myself, let's hope I haven't forgot any parenthesis comma, single quote, etc.

[fordwayrichardg]

Thanks efialttes. I had already thought about sub-splitting the string but it is not elegant as you say.

The fact that the number is accepted as-is without formatDateTime suggests that the string is okay. It's just a case of figuring out what I have done wrong with the formatDateTime function (I hope).

[efialttes]

Indeed. Let's hope there is finally no need for subsplitting 😄

[v-xida-msft]

Hi @fordwayrichardg,

Do you want to format your UK format date time into ISO format with formatDateTime() function?

Could you please share a bit more about your formatDateTime() formula?

I think there is something wrong with the usage of formatDateTime() function on your side. The first parameter of formatDateTime() function requires a datetime string which match ISO 8601 format (‘o’), but date time string ‘27/11/2017/ 17:00:00’ that you provided could not match standard ISO 8601 format, the default format of the required datetime string is ‘MM/dd/yyyy HH:mm:ss’.

More details about formatDateTime() function, please check the following article:

formatDateTime() function

Best regards,

Kris

[efialttes]

Hi!

Then, string manipulation (subsplitting, etc.) is the only chance in this case, right?

Thank you in advance!

[fordwayrichardg]

Hi Kris.

This is the exact formula I have at the moment:

formatdatetime(outputs('Compose_End_Date_and_Time_(Split)')[1],'dd/MM/yyyy hh:mm:ss')

Are you saying, therefore, that the string parameter at the end, 'dd/MM/yyyy hh:mm:ss' is not for telling formatDateTime the format that the incoming string is in but actually for determining the output format? If so, efialttes suggestion of using sub-string splitting may be the only option #SadFace.

[fordwayrichardg]

Well in spite of a better solution, I have gone with the suggestion of efialttes and I have setup some split steps to break-down the date and time and then rebuild it. The Flow works so thank you very much for the suggestions and solution.