Unanswered

Unable to process template language expressions in 'formatDateTime'

(1) Share

Report

Posted on by richardj5

Have set up an excel table in sharepoint with dates for mid, final and extended probation's of staff, added additional columns showing reminder dates at 7 days before, 1 day before etc created individual flows that reads the date in the appropriate reminder columns, compares to today's date and send e-mail reminder to those who need to carry out the reviews if the date matches, so have 6 flows as below

Extended Probation 1st Reminder
Extended Probation Review 2nd reminder
Mid Probation 1st Reminder
Mid Probation 2nd Reminder
Final Probation 1st Reminder
Final Probation 2nd Reminder

They were all created as copy's of each other (as after creating the first one and it working I just copied and amended the columns to read from), but only 2 of them work, (Mid Probation 1st Reminder and Mid Probation 2nd Reminder) the other 4 all fail with the following error

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

Below is the working formulas for the "Mid Probation 1st Reminder" and "Mid Probation 2nd Reminder" as an example

formatDateTime(items('Apply_to_each_4')?['Mid Reminder Date 1 (7 Days)'],'dd/MM/yyyy')

formatDateTime(items('Apply_to_each_4')?['Mid Reminder Date 2 (1 day)'],'dd/MM/yyyy')

For all the others I have just copied the above and changed to the relevant columns needed from the table (as per below), but they all fail

formatDateTime(items('Apply_to_each_4')?['Final Reminder Date 1 (7 Days)'],'dd/MM/yyyy')

formatDateTime(items('Apply_to_each_4')?['Final Reminder Date 2 (1 day)'],'dd/MM/yyyy')

formatDateTime(items('Apply_to_each_4')?['Extended Date 1 (7 days)'],'dd/MM/yyyy')

formatDateTime(items('Apply_to_each_4')?['Extened Date 2 (1 day)'],'dd/MM/yyyy')

The table is all formatted exactly the same for all the dates (screenshot below), so I'm scratching my head as to why 2 work, but 4 don't?

Any help appreciated

Thanks

Categories:

Building flows

I have the same question (0)

All responses (12)

Answers (0)

richardj5 53 on at

Like (2)

Report

Fixed it myself, flow did not like the blanks in the table, entered fictional dates of 01/01/2099 and all are now working 😁

Was this reply helpful? Yes No
Sayan 818 Super User 2025 Season 2 on at

Like (1)

Report

@richardj5 Great work !

Was this reply helpful? Yes No
richardj5 53 on at

Like (1)

Report

Well I thought it was fixed, the tests ran successfully, but the automated runs bought back the same error?

Was this reply helpful? Yes No
Sayan 818 Super User 2025 Season 2 on at

Like (1)

Report

Hey @richardj5

Thanks for your response. can you please share the whole workflow?

------------------------------------------------------------------------------------------------------------------------------
If my suggestion helped you, please give it a Thumbs up 👍 and Mark it as a Solution 🎉 .
Sayan Patra
RPA Developer

Was this reply helpful? Yes No
richardj5 53 on at

Like (2)

Report

Hi

One of the failed flows below, all worked when tested yesterday, 3 worked automatically this morning and 3 failed, no changes made to the spreadsheet/table overnight and all the flows are copy's of each other with just formula in the condition amended on each one to read the correct column in the spreadsheet?

Was this reply helpful? Yes No
Sayan 818 Super User 2025 Season 2 on at

Like (1)

Report

Hi @richardj5 Thanks for sharing the workflow.

In the condition where you are checking the date, an error occurs because the date is not in a valid format. Please check the Data is in Valid format or not.

Have you checked whether the formatDate function gives the correct output?
There are some empty rows and N/A in the Excel sheet. Please add a condition to skip the empty rows.

Also, if possible, can you please share what you are actually trying to do? I am a bit confused. If I get more clarity about it, it will be easier to solve the problem.

------------------------------------------------------------------------------------------------------------------------------
If my suggestion helped you, please give it a Thumbs up 👍 and Mark it as a Solution 🤖.

Thanks,
Sayan Patra
RPA Developer

Was this reply helpful? Yes No
richardj5 53 on at

Like (2)

Report

Hi

As in original post, trying to read a date from spreadsheet in SharePoint, if date matches today's date email is sent.

I also believed it was caused by the blanks and N/A's in the file, as when dummy dates added test run worked fine yesterday

But auto run this morning failed?

Was this reply helpful? Yes No
Sayan 818 Super User 2025 Season 2 on at

Like (1)

Report

Hey @richardj5

can you please share the dummy data and inside the condition what is the expression you have written?
Are you using new designer or Classic Designer ?

I can see that there is 89 rows in the sheet. can you please In the for each check that what the error coming?

------------------------------------------------------------------------------------------------------------------------------If my suggestion helped you, please give it a Thumbs up and Mark it as a Solution.
Thanks,
Sayan Patra
RPA Developer

Was this reply helpful? Yes No
richardj5 53 on at

Like (2)

Report

Hi

Employee probation spreadsheet attached (names removed for confidentiality)

Condition for the failed flows when auto ran (succeeded when tested manually)

formatDateTime(items('Apply_to_each_4')?['Extended Date 1 (7 days)'],'dd/MM/yyyy')
formatDateTime(items('Apply_to_each_4')?['Extended Date 2 (1 day)'],'dd/MM/yyyy')

Error in the for each when flow failed

Versus successful flow run (all outputs false as no dates matched to today's date)

Employee Probatio...

Was this reply helpful? Yes No
Sayan 818 Super User 2025 Season 2 on at

Like (1)

Report
Hey @richardj5

Finally, the issue resolved!
The issue is caused due to the Date Format.
In Power Automate Cloud, the date format "45678" corresponds to an Excel serial date number. To convert this serial number to a date in Power Automate Cloud follow the following solution -

Solution:

Step 1:
Initialize Variable which holds the Today () Date

Step 2:
Read the Date Using Action "List rows present in a table

Step 3:

Use Apply to each
inside for-each use a condition

addDays('1899-12-30', int(item()?['Mid Reminder Data']), 'dd-MM-yyyy')
use the above expression on the Right-hand side to convert the Excel Data to a Date Format.
In Power Automate Cloud, you can use the addDays function to convert the Excel serial date number to a readable date.

addDays('1899-12-30', 45481, 'yyyy-MM-dd')
This formula will convert the serial number 45481 to the date 2024-07-08.

Main WorkFlow:

Hope It will solve the issue!

------------------------------------------------------------------------------------------------------------------------------
If my suggestion helped you, please give it a Thumbs up 👍 and Mark it as a Solution 🤖. This motivates me to solve more problems.
Thanks,
Sayan Patra
RPA Developer

Was this reply helpful? Yes No