Power Auto formatdatetime

[TomDennison_BDP]

Hi: I came across an error that Power Automate reports as "this is a new issue, try posting about it.

• I have an Excel spreadsheet that has dates formatted as Date and the format of 3/14/21.
• I am moving them via Power Automate into SharePoint where the receiving fields are formatted as "Date and Time" and using the standard format.
• In Power Automate, I am using a formula formatDateTime(items('Apply_to_each_2)?['PlannedStartDate'], 'MM-dd-yy')
• The editor says this is a valid format at design time.
• At runtime however, the flow fails and I get an error "InvalidTemplate. Unable to process language expressions in action 'Update_item" inputs at line '1' and column '28793': The template language function 'formatDateTime' expects its first parameter to be of type string. The provided value is of type NULL. Please see https://aka.ms/logicexpressions#formatdatetime for usage details.:
• The field is not returning null as they used to be formatted as strings in both the source Excel file and SharePoint. However, I have reformatted both locations to the same date format. 

Any ideas what the issue might be?

Thanks!

Tom

[Pstork1]

SharePoint always stores Dates as Date and Time values in Universal Time Code (UTC) timezone.  When there is no time associated with a Date it stores the time portion as midnight.  So when inputting a DateTime to SharePoint it should be formatted as an ISO 8601 timespan. To do that simply change your formula to use the UTC ISO 8601 format.   FormatDateTime will supply the midnight timecode.  Here's the formula you should use

formatDateTime(items('Apply_to_each_2)?['PlannedStartDate'], 'o')

The one you are using is valid for display or insertion into an email, but not into a SharePoint column.

[TomDennison_BDP]

Hey! Thanks for the reply. Unfortunately, I am still having a similar issue. With the new format of:

formatDateTime(items('Apply_to_each_2')?['PlannedStartDate'], 'o')

I now get the error: 

InvalidTemplate. Unable to process template language expressions in action 'Update_item' inputs at line '1' and column '28794': 'The template language function 'formatDateTime' expects its first parameter to be of type string. The provided value is of type 'Null'. Please see https://aka.ms/logicexpressions#formatdatetime for usage details.'.

[TomDennison_BDP]

I found one bug - I was populating the value FROM the SharePoint list back TO the SharePoint list which WAS NULL. I fixed it and now pulling from the Excel file that does contain a date and now I am getting the following error:

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

[Hardesh15]

@TomDennison_BDP Please add one compose and pass your excel date column value from dynamic content. Check compose output after run. if it is returning any serial number it means that you need to convert it into proper date for update action.

For few regions, Excel Date and Time is coming as date and time not any serial number.

Did my reply help? Please give it a thumbs up.
Did I answer your question? Please mark my post as a solution!
Regards,
Hardesh

[TomDennison_BDP]

Well, I think we are getting closer. I did find using a Compose that the date is in fact coming in as a serial number. I did some Googling on how to convert it and found:

addDays(items('Apply_to_each')?['RP Start Date'], int(outputs('Compose')),'dd-MM-yyyy')

The error message is now: 

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

The source date in Excel is 2019-01-08 and appears in Power Automate as 42430. 

Thoughs?

[Hardesh15 · Accepted solution]

@TomDennison_BDP  Pls use following expression-

addDays('1899-12-30',int(outputs('Compose')),'dd-MM-yyyy')

Did my reply help? Please give it a thumbs up.
Did I answer your question? Please mark my post as a solution!
Regards,
Hardesh