Answered

Compare Dates

(0) Share

Report

Posted on by sabeoleonunes

Hi
I have created a flow wherein i need to compare 2 dates columns from 2 different excel sheets from SharePoint documents , but the flow is giving the following error , Unable to process template language expressions for action 'Condition' at line '1' and column '15509': 'In function 'formatDateTime', the value provided for date time string '35827' was not valid. The datetime string must match ISO 8601 format.'.

Categories:

General topics

All responses (6)

Answers (1)

Sort by

CFernandes 8,504 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @sabeoleonunes

As per my understanding, the dates returned by your Excel are not in ISO 8601 format (2020-08-14T13:56:59)

Can you post the screenshot of the DateTime returned by Excel in your Power Automate(add a compose action and log the Excel DateTime value)?
sabeoleonunes 79 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @CFernandes , My agenda is i need to create a flow wherein i need to compare 2 excel files which have column date in it , so i need to compare both and if the dates are not matching then send a mail to the user saying , the mail are not matching
I am sharing my flow what i did , i have used the date expression as :
formatDateTime(items('Apply_to_each')?['date'], 'MM/dd')

The error which i am getting is :
InvalidTemplate. Unable to process template language expressions for action 'Condition' at line '1' and column '15453': '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.'.
sabeoleonunes 79 on at

Like
a
(0)

Report
Copy link

Link copied!

Sending the flow again as it is not clear
CFernandes 8,504 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

@sabeoleonunes Can you please share the screenshot of your excel file( I want to see how your date field appears).

I also did a Quick google and found a thread with solution provided by @Pstork1 at https://powerusers.microsoft.com/t5/General-Power-Automate/Compare-Dates/m-p/670569#M56353
sabeoleonunes 79 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @CFernandes ,
This is the first excel

This is the second excel
Verified answer

CFernandes 8,504 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!

Hi @sabeoleonunes

I am just doing this on the fly.

Formula Format Date -1 -

formatDateTime(outputs('Date_1'),'yyyy-MM-dd')

Fomula Format Date - 2-

formatDateTime(outputs('Date_2'),'yyyy-MM-dd')

Condition Formula -

ticks(outputs('Format_Date_1')) is greater than
ticks(outputs('Format_Date_2'))

I am not 100% sure but I think ticks function turns the dates into a number representing the date. Give the above a try.