Inconsistent Date Format Error

[NamrataKCurijo]

Hi all

I would really appreciate a solution to this recurring but inconsistent error in my flow.

My automated cloud flow is triggered by submitting an MS Form.

In the MS Form, I ask for multiple dates of birth of various parties, amongst other information.

I then use the data from the MS Form to auto-populate an MS Word template, create a file, and then send an automated email with the file attached.

The default date format that gets populated in the Word Doc is "yyyy-MM-dd", which I understand is the ISO 8601 date format.

I want to convert the date format to "dd MMMM yyyy", so in each of the DOB fields, I have an expression, which looks like this:

formatDateTime(outputs('Get_response_details')?['body/r3d7a8c854c0e44539c19ff4190625795'], 'd MMMM yyyy', 'en-AU')

The text in square brackets varies for each expression.

My MS Form has 196 questions, and each of them has a corresponding field in the MS Word template. 

The issue is this:

On submitting the MS Form, my flow sometimes seems to work perfectly and goes all the way to the end and sends the email. However, sometimes, it gets stuck at the "Populate a MS Word template" step, and reports this error:

Unable to process template language expressions in action 'Populate_a_Microsoft_Word_template' inputs at line '0' and column '0': 'In function 'formatDateTime', the value provided for date time string '' was not valid. The datetime string must match ISO 8601 format.'.

I have used the above expression in another flow that deals with only one DOB, and it works perfectly.

In this flow, on using the above expression for 4-5 DOBs, it seems to work fine.

When I start to populate the expressions for the remaining DOBs, I start to get this error.

I look forward to any suggestions, comments and solutions from the community.

Kind regards

NK

[SanmeshG]

Hi @NamrataKCurijo  ,

You need to ensure that the value provided for the data of birth(date value) is not null.

To convert a date into ISO 8601 you can format the date expression as below.

formatDateTime(outputs('Date'),'yyyy/MM/dd')

Also if date of birth is not a required field in your form , then you might first want to check if the value is present or not and only then format it in ISO 8601 format.

The expression for the same would be as below.

If(not(empty(outputs('Date'))),formatDateTime(outputs('Date'),'yyyy/MM/dd'),null)

Hope this helps!

Please give this a thumbs up and accept this as a solution.

Thanks,
Sanmesh

[NamrataKCurijo]

Thank you @SanmeshG 

Does 'Date' in your expression represent dynamic content?

In that case, would we leave it as 'Date' or replace it with the relevant dynamic content code (apologies, I don't know the exact technical term for this). E.g. 

formatDateTime(outputs('Get_response_details')?['body/r3d7a8c854c0e44539c19ff4190625795'], 'd MMMM yyyy', 'en-AU')

The date format I want in the output is "20 September 2023". 

Regards

NK

[SanmeshG]

Hi @NamrataKCurijo  ,

Yes it is the dynamic value that you want to pass an an input.

Below is the expression if you want it , as  20 September 2023.

formatDateTime(outputs('Date'),'d MMMM yyyy')

the expression you mentioned is correct!

[SanmeshG]

@NamrataKCurijo  ,

Here you have to ensure that the input date format is in ISO 8601 format else you will receive the error that you have mentioned

[NamrataKCurijo]

Thanks @SanmeshG! Really appreciate your help.

What do I do to ensure that the input date is in ISO 8601 format?

If this expression is correct, do you know why the flow came up with the error I mentioned in my first message?

formatDateTime(outputs('Get_response_details')?['body/r3d7a8c854c0e44539c19ff4190625795'], 'd MMMM yyyy', 'en-AU')

Regards

NK

[SanmeshG]

Hi @NamrataKCurijo  ,

I don't think there is any expression that helps you find out if the date is in ISO 8601 format. But usually the date saved from the form should be in a specific format which will be the same for all responses.

[NamrataKCurijo]

Hi @SanmeshG 

I am using the expression template you gave in your first response to check if the value is blank, however I am getting the message that the expression is invalid. Could you please check what might be wrong with this?

If(not(empty(outputs('Get_response_details')?['body/ra9d1634b1d7c4b52a51b22457c9f2fbd']))),formatDateTime(outputs('Get_response_details')?['body/ra9d1634b1d7c4b52a51b22457c9f2fbd'],'d MMMM yyyy'),null)

Many thanks

Namrata

[SanmeshG]

HI @NamrataKCurijo  ,

You have an extra closing parenthesis. please try the below.

If(not(empty(outputs('Get_response_details')?['body/ra9d1634b1d7c4b52a51b22457c9f2fbd'])),
formatDateTime(outputs('Get_response_details')?['body/ra9d1634b1d7c4b52a51b22457c9f2fbd'],'d MMMM yyyy'),null)