cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TomDennison_BDP
Helper I
Helper I

Power Auto formatdatetime

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

 

1 ACCEPTED SOLUTION

Accepted Solutions

@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

View solution in original post

6 REPLIES 6
Pstork1
Dual Super User III
Dual Super User III

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.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

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.'.

 

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.'.

@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

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?

@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

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors
Users online (2,893)